Perform Batch Inserts/Updates in SQL Server 2005 using XML

My current project requires me to download huge amounts of data from a remote server in the form of XML and save them to our local database. Lets see if we can do this using just one simple T-SQL stored procedure. Lets first create our table. For the purpose of this demonstration I will use a simple schema with the following signature

   1:  CREATE TABLE [dbo].[Employee](

   2:      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,

   3:      [Name] [nvarchar](50) NOT NULL,

   4:      [Gender] [varchar](1) NOT NULL,

   5:      [DateOfBirth] [datetime] NOT NULL,

   6:   CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 

   7:  (

   8:      [EmployeeID] ASC

   9:  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

  10:  ) ON [PRIMARY]

Next, we need to create the stored procedure that will do the batch insert for us.

CREATE PROCEDURE [dbo].[spr_Employee_ProcessXmlData](@employees xml)
AS
BEGIN
    DECLARE @TempTable TABLE
    (
        [Name] [nvarchar](50),
        [Gender] [varchar](1),
        [DateOfBirth] [datetime]
    );

    -- Populate the Temporary table from the XML
    INSERT INTO @TempTable (Name, Gender, DateOfBirth)
        SELECT
                ParamValues.Data.value('Name[1]','nvarchar(50)'),
                ParamValues.Data.value('Gender[1]','varchar(1)'),
                ParamValues.Data.value('DateOfBirth[1]','datetime')
        FROM
            @employees.nodes('NewDataSet/Employee') as ParamValues(Data);

    INSERT INTO Employee(Name, Gender, DateOfBirth)
        SELECT T.* FROM @TempTable T WHERE T.Name NOT IN (SELECT Name FROM Employee)
END

I won’t go into details about the XML programming – this great article are among several others that provide a good start for those unfamiliar with this feature of T-SQL. The stored procedure first creates a temporary in-memory table which will hold the data from the XML. Each node is read from the XML and mapped to the appropriate column of the temporary table. The temporary table is merely used to prevent duplicate data from being inserted into our actual table. If you are certain that your XML will never contain redundant data, then you can skip this step and directly insert the data to the Employee table.

We are now ready to perform batch inserts! The following SQL script demonstrates how.

EXEC    [dbo].[spr_Employee_ProcessXmlData]
        @employees = '<NewDataSet>
                        <Employee>
                            <Name>Raihan Iqbal</Name>
                            <Gender>M</Gender>
                            <DateOfBirth>11-05-1983</DateOfBirth>
                        </Employee>
                        <Employee>
                            <Name>John Doe</Name>
                            <Gender>M</Gender>
                            <DateOfBirth>10-05-1972</DateOfBirth>
                        </Employee>
                    </NewDataSet>'

It is needless to say that we can generate the XML and execute the stored procedure using any .NET language.

Happy programming!