Search This Blog

Tuesday, November 1, 2011

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

3 comments :

  1. Every thing is fine But where is insert query :)

    ReplyDelete
  2. its left for you.. to do some exercise your self and write insert command as per your need

    ReplyDelete
  3. *Oh my goodness! an amazing article dude. Thank you However I am experiencing issue with ur rss . Don’t know why Unable to subscribe to it. Is there anyone getting identical rss problem? Anyone who knows kindly respond. Thnkx more information

    ReplyDelete