Insert xml data into SQL server 2008 using c#

In this article I am going to explain how to insert xml data into SQL using c#.

Description

In this article I take  "Data Table" and that "Data Table" convert into xml and that xml insert into SQL table using a Stored Procedure.

Firstly I created a table in SQL

CREATE TABLE Employee (                 EmpId INT identity(1, 1),                 NAME VARCHAR(50),                 MobileNo VARCHAR(13)                 )

Now Code In C# to create a "Data Table" that return 5 rows.

//Create a DataTable that retuen 5 rows     private DataTable GetTable()     {         DataTable dt = new DataTable();         dt.Columns.Add("NAME");         dt.Columns.Add("MobileNo");           for (int i = 0; i < 5; i++)         {             DataRow rr = dt.NewRow();             rr["NAME"] = "Sharad" + i.ToString();             rr["MobileNo"] = "99787878788" + i.ToString();             dt.Rows.Add(rr);         }         return dt;       }

Now I code to convert "Data Table" into "XML".

//Convert DataTable to XML     public string ConvertDataTableToXMLDataString(DataTable dataTbl)     {         StringBuilder XMLString = new StringBuilder();           if (string.IsNullOrEmpty(dataTbl.TableName))             dataTbl.TableName = "DataTable";         XMLString.AppendFormat("<{0}>", dataTbl.TableName);           DataColumnCollection tableColumns = dataTbl.Columns;         foreach (DataRow row in dataTbl.Rows)         {             XMLString.AppendFormat("<NewDataSet>");             foreach (DataColumn column in tableColumns)             {                 XMLString.AppendFormat("<{1}>{0}</{1}>", row[column].ToString(), column.ColumnName);             }             XMLString.AppendFormat("</NewDataSet>");         }         XMLString.AppendFormat("</{0}>", dataTbl.TableName);         return XMLString.ToString();     }

Now I code to Save Xml Data in SQL table, for it I write a stored procedure.

CREATE PROC InsXmldata (@xml_data VARCHAR(MAX) = '') AS BEGIN                 DECLARE @XMLDocHandle INT                   IF @xml_data <> ''                 BEGIN                                 EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT,                                                 @xml_data                                   INSERT INTO Employee (                                                 NAME,                                                 MobileNo                                                 )                                 SELECT  NULLIF(NAME, ''),                                                 NULLIF(MobileNo, '')                                 FROM OPENXML(@XMLDocHandle, N'/Employee/NewDataSet', 2) WITH (                                                                 NAME VARCHAR(50),                                                                 MobileNo VARCHAR(13)                                                                 )                                   EXEC sp_xml_removedocument @XMLDocHandle                 END --- @xml_data END END

Now I code to Save Xml Data into Database (You can write the given below code on button click).

DataTable dt = GetTable();         dt.TableName = "Employee";         string XmlDoc = ConvertDataTableToXMLDataString(dt);           SqlConnection con = new SqlConnection("data source=.;Database=test;uid=sa;pwd=*************;");         con.Open();         SqlCommand cmd = new SqlCommand("InsXmldata", con);         cmd.Parameters.AddWithValue("@xml_data", XmlDoc);         cmd.CommandType = CommandType.StoredProcedure;         int i =cmd.ExecuteNonQuery();

Full aspx.cs code

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Text; public partial class _Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {       }       //Create a DataTable that retuen 5 rows     private DataTable GetTable()     {         DataTable dt = new DataTable();         dt.Columns.Add("NAME");         dt.Columns.Add("MobileNo");           for (int i = 0; i < 5; i++)         {             DataRow rr = dt.NewRow();             rr["NAME"] = "Sharad" + i.ToString();             rr["MobileNo"] = "99787878788" + i.ToString();             dt.Rows.Add(rr);         }         return dt;       }       //Convert DataTable to XML     public string ConvertDataTableToXMLDataString(DataTable dataTbl)     {         StringBuilder XMLString = new StringBuilder();           if (string.IsNullOrEmpty(dataTbl.TableName))             dataTbl.TableName = "DataTable";         XMLString.AppendFormat("<{0}>", dataTbl.TableName);           DataColumnCollection tableColumns = dataTbl.Columns;         foreach (DataRow row in dataTbl.Rows)         {             XMLString.AppendFormat("<NewDataSet>");             foreach (DataColumn column in tableColumns)             {                 XMLString.AppendFormat("<{1}>{0}</{1}>", row[column].ToString(), column.ColumnName);             }             XMLString.AppendFormat("</NewDataSet>");         }         XMLString.AppendFormat("</{0}>", dataTbl.TableName);         return XMLString.ToString();     }     protected void BtnSaveXmlData_Click(object sender, EventArgs e)     {           try         {             DataTable dt = GetTable();             dt.TableName = "Employee";             string XmlDoc = ConvertDataTableToXMLDataString(dt);               SqlConnection con = new SqlConnection("data source=.;Database=test;uid=sa;pwd=*************;");             con.Open();             SqlCommand cmd = new SqlCommand("InsXmldata", con);             cmd.Parameters.AddWithValue("@xml_data", XmlDoc);             cmd.CommandType = CommandType.StoredProcedure;             int i = cmd.ExecuteNonQuery();             if (i > 0)             {                 lblmesg.Text = "Save Successfully";             }             else             {                 lblmesg.Text = "Some Error Occured";             }         }         catch(Exception ex) {             lblmesg.Text = ex.Message.ToString();         }     } }

Code of aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">   <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title></title> </head> <body>     <form id="form1" runat="server">     <div>     <asp:Button ID="BtnSaveXmlData" runat="server" Text="Save"             onclick="BtnSaveXmlData_Click" />             <asp:Label ID="lblmesg" runat="server"></asp:Label>     </div>     </form> </body> </html>