Showing posts with label XML. Show all posts
Showing posts with label XML. Show all posts

Saturday, April 16, 2011

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>
 <Trans TransId="1" Add="false" Edit="true" Delete="true" View="true" Block="false">   
 </Trans>
 <Trans TransId="2" Add="1" Edit="1" Delete="1" View="1" Block="0">   
 </Trans>
</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 table #TempTable

--
happy coding