Friday, July 8, 2011

How to insert complete dataset values into sql server 2005+ using XML

First we have to convert the ds into xml and pass it to the stored

procedure.
For the sake of simplicity i created here a xml schema @xml-schema and temporary table #TempTable.


DECLARE @ixml int
DECLARE @xml-schema varchar(max)
SET @xml-schema ='





'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @ixml OUTPUT, @xml-schema
-- Execute a SELECT statement that uses the OPENXML rowset provider.


SELECT *
Into #TempTable
FROM OPENXML(@ixml, '/ROOT/Trans',1)
WITH
(
TransId varchar(10),
[Add] bit,
Edit bit,
[Delete] bit,
[View] bit,
Block bit
)

Select * From #TempTable

drop table #TempTable




You can change it as per your requirement . have any query please contact me rahularyansharma@gmail.com

2 comments:

  1. One Question.
    what can be the size of DataSet. because using varchar as a parameter i can send only 8000 characters.
    so what should i do for if my generated xml's length is greater than 8000 chars.

    ReplyDelete
  2. Hi ajay

    sorry i forgot to check comments on this blog but you can use nvarchar(max) in which i think you can use around 2gb data.so i think no problem with storage or xml size.

    ReplyDelete