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