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