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

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.

Into #TempTable
FROM OPENXML(@ixml, '/ROOT/Trans',1)
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


  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.

  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 i think no problem with storage or xml size.