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.

How to copy a row within same table ?

If you want to copy a row in a datatable to the same table you can use ImportRow.

Example :

Suppose a DataTable dtUser has some rows and you want to add another row from the same table.
dtUser.ImportRow(dtUser.Rows[0]);

Why This Blog ?

Hello
My name is Khoja Mahmadhusen Rafiqhusen. A Software Developer From A Very Small Village Of Gujarat. I am at present working in Mumbai as a software developer in .net web technology. I am starting this blog to write all the possible things I am learning from my day to day work to help developers like me. Any suggestions or posts are always welcome. Please write to me on mahmad_khoja@yahoo.co.in.