Use of XML DataType | How to Insert or Update multiple Rows From GridView to Stored Procedure

 

How to Insert All the Rows From Following GridView To Database Table.

If you see, Generally we find most of the people writing For or While Loops and multiple iterations to Insert Rows Into Database.

The above Approach causes Multiple Hits to database which may degrade the performance of database.

Let see how XML Datatype can Help us in Optimizing this task.

 

Write a For loop to Generate String As Following

<table>

<productid>17</productid>

<productname>Alice Mutton</productname>

<unitprice>39.0000</unitprice>

</table>

If you See Its Simple HTML with Opening and closing tags. Just Loop it and you  will have Following String.

Each row should have Opening <Table> and closing </Table> Tags with  Opening and Closing Field Tags.

string mystr=string.Empty;

mystr=’<table>

<productid>17</productid><productname>Alice Mutton</productname>

<unitprice>39.0000</unitprice>

</table>

<table>

<productid>3</productid><productname>Aniseed Syrup</productname>

<unitprice>10.0000</unitprice>

</table>

<table>

<productid>40</productid><productname>Boston Crab Mutton</productname>

<unitprice>18.4000</unitprice>

</table>

<table>

<productid>60</productid><productname>Camembert Pierrot</productname>

<unitprice>34.0000</unitprice>

</table>

<table>

<productid>18</productid><productname>Carnarvon Tigers</productname>

<unitprice>62.5000</unitprice>

</table>’

Now You can Pass this String as XML Parameter To Stored Procedure Which Executes Following Query To Extract Result From XML Parameter.

Declare @xmlData Xml;

Set @xmlData=mystr;   -- Assigning XML String to XML DataType

 

How to Read Data From Xml Variable

Note : Field name as Case Sensitive and DataType Should Match.

Select x.data.value('productid[1]','int') as ProductId,
x.data.value('productname[1]','varchar(50)') as ProductName,
x.data.value('unitprice[1]','decimal(12,4)') as UnitPrice
From @xmlData.nodes('/table') as x(data)

 

 

Note :  As Xml Doesn’t Support Following Symbols

& , < , >

We Need Replace These Symbols In XmlString Before Passing string Value to Store Procedure Parameter  or XMLVariable.

With Following Characters

&  --  &amp;

<  --   &lt;

>  --  &gt;

the Output of Query can be Used To Update and Insert Data In a Table