Bulk DML With SQL Server 2005 - XML Data Type

I would like to show how we can use SQL Server 2005's XML datatype for inserting / updating / deleting multiple records. When we have 100's of records which has to be processed in SQL server 2005 best way of doing it is passing the records as a XML instead of looping it from the front end and calling the Stored procedure N number of times. Below are the steps to use XML effectively in SQL server programming..

Step 1:

Create a stored procedure with XML Data type as a input paramter

Step 2:

Count the number of rows in passed XML parameter ( Specify the XML Schema inside )

@TotalNoOfRecords = @insertxml.query('<count>{count(/Info/employee)}</count>').value('count[1]','int')

Step 3:

Write your DML statement inside the "WHILE" loop ,

INSERT INTO tblXMLTest(name,city) Sample Insertion

SELECT InsertXML.value('@name[1]','varchar(40)'), insertxml.value('@city[1]','varchar(40)') FROM @InsertXML.nodes('/Info/employee[position()=sql:variable("@i")]') e(InsertXML)     

Complete example of using XML Datatype for Bulk XML Insert / Update / Delete

Sample SQL Code

DECLARE @InsertXML XML -- Which will be supplied from stored procedure

SET @InsertXML = '<Info>
  <employee name="Sridhar" city="Boston"></employee>
  <employee name="Subramanian" city="Dallas"></employee>
  <employee name="JoeBiden" city="New York"></employee>
</Info>'

DECLARE @TotalNoOfRecords INT, @i INT
SELECT @TotalNoOfRecords = @insertxml.query('<count>{count(/Info/employee)}</count>').value('count[1]','int')
SET @i=1
WHILE @i <=@TotalNoOfRecords

BEGIN
    INSERT INTO tblXMLTest(name,city) Sample Insertion
    SELECT InsertXML.value('@name[1]','varchar(40)'), insertxml.value('@city[1]','varchar(40)')
    FROM @InsertXML.nodes('/Info/employee[position()=sql:variable("@i")]') e(InsertXML)
-- Increment Loop
    SET @i = @i + 1
END