How To Send DataSet As An XML to SQL Server 2005 Stored Procedure ? Pass DataSet To Stored Procedure

There is some time when we want to send the result of a dataset to send to a stored

procedure without using loop as it is time and resource consuming. Here I am giving an

example how we can send DataSet as XML To Stored Procedure As A Parameter.

Suppose DataSet dsStudent has One table at position 0. Having three columns
GrNo
Name
City

And suppose it has 10 records in front end and you want to send as it is to stored

procedure to make any operation , say to insert into an actual table.

First of all you have to make an input parameter for your stored procedure and send

the dataset as an xml.

dtStudent.GetXml();

This method will send you dataset as a whole to the stored procedure as a parameter.

It will send the parameter’s table as ‘/NewDataSet/Table’. If you have specified a

name to table for dataset instead of Table it will be the name specifi ed by you.

–Declare A Variable To Prepare XML Document
Declare @idoc int

exec sp_xml_preparedocument @idoc OUTPUT,@Param1 –This Statement Will Prepare your

xml document where @Param1 is the parameter passed by you of type XML.

Now let’s insert the data from dataset to the table in SQL Server 2005

Insert Into dbo.Mst_Student
(GrNo,Name,City)

Select GrNo,Name,City
From OpenXML (@idoc,’/NewDataSet/Table’,2)
With (GrNo int,Name Varchar(50),City Varchar(50))

exec sp_xml_removedocument @idoc

This statement will insert all the records from ‘/NewDataSet/Table’ To your actual

table in SQL Server 2005 “Mst_Student”

Please do remember to remove the xml document from memory.