How to pass XML/Datatable/Dataset in Store Procedure (SP) from C# from code behind in ASP.net

Every time we face problem of passing multiple value in store procedure to perform the following task

1)      Multiple insert in table

2)      Select multiple rows using  in-clause at where condition

3)      Delete multiple rows

Here I am writing some very good code to pass multiple value in store procedure using xml string which is much faster and we can pass a large value having multiple rows and column some time using Gridview and other similar control.

Convert  Datatable/Dataset to XML string

public string DataTableToXML(DataTable table)

{

StringWriter xml = new StringWriter();

string xmldata = string.Empty;

table.TableName = "journal";

table.WriteXml(xml,XmlWriteMode.IgnoreSchema);

xmldata = xml.ToString();

return xmldata;

}

Handling of XML string inside Store procedure/Query

Pass this xml string in your store procedure and handle this string in your SP by as following code given

Create procedure handlexml(@journalstring varchar(1000))

As

Begin

/*declare @journalstring varchar(2000)='<DocumentElement>

  <journal>

    <jrnlfk>95</jrnlfk>

    <orderfk>18</orderfk>

  </journal>

  <journal>

    <jrnlfk>165</jrnlfk>

    <orderfk>2</orderfk>

  </journal>

  <journal>

    <jrnlfk>115</jrnlfk>

    <orderfk>2</orderfk>

  </journal>

</DocumentElement>' */ 

Suppose your string is xml element/Node and coming as above format

------------------------------------------

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT,

@journalstring

---------------------------------------------For XML Element--------------------------

SELECT jrnlfk,orderfk

FROM OPENXML(@hdoc, 'DocumentElement/journal',3)

WITH (jrnlfk int,

orderfk int)

-----------------------------------For XML Attribute--------------------------

declare @journalstring varchar(2000)='<Journal>

<remit journalfk="165" orderno="25"/>

<remit journalfk="197" orderno="15"/>

</Journal>'

---------------------------------------------------XXX-----------------------

SELECT *

FROM OPENXML(@hdoc, 'Journal/remit', 2)

WITH (journal int '@journalfk',

orderno int '@orderno')

end

Hope this help you

Happy Programming