How to insert multiple record using xml in sql server 2005+

How to insert the multiple record using the xml into sql server, please check the following example.

DECLARE @idoc int

DECLARE @doc varchar(max)

SET @doc =’

<ROOT>

TransId=”1″ Add=”false” Edit=”true” Delete=”true” View=”true” Block=”false”>

</ROOT>’

–Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

– Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT * Into #TempTable

FROM OPENXML

(@idoc, ‘/ROOT/Trans’,1)

WITH (

TransId varchar(10),

[Add] bit,

Edit bit,

[Delete] bit,

[View] bit,

Block bit )

Select * From #TempTable

– drop temp table

drop table #TempTable

–

happy coding

Advertisement