Asp.net:Multiple insertion of record into a SQL Server database table using XML

Every time we face problem bulk insertion through 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
      Save data from grid to database table in sql server 2005/2008

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.

This is the best and optimize method to insert multiple records in one go from asp.net to database table in sql server 2005/2008 using the XML formatting.

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;

}

the out put XML of the above code will be as given in the store-procedure variable.

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 -- end of procedure

Hope this help you

Happy Programming