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 

No comments:

Post a Comment

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...