How to pass XML as parameter in SQL Stored Procedure

Welcome everybody,

Here I am going to explain you, how we can pass the xml document as stored procedure parameter.

Here we go,

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml;

public partial class xmlToSQL : System.Web.UI.Page

{

    SqlConnection dbConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ToString());

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindXMLGridView();

        }

    }

    protected void BindXMLGridView()

    {

        XmlTextReader xRed = new XmlTextReader(Server.MapPath("studentDetails.xml"));

        DataSet Ds = new DataSet();

        Ds.ReadXml(xRed);

        SqlCommand cmd = new SqlCommand("usp_GetXMLParameter", dbConn);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("xmlParam", Ds.GetXml()).DbType = DbType.Xml;

        dbConn.Open();

        SqlDataAdapter Da = new SqlDataAdapter(cmd);

        DataTable Dt = new DataTable();

        cmd.Connection = dbConn;

        Da.Fill(Dt);

        DataView dv = new DataView(Dt);    //here I have sorted my Column using DataView

        dv.Sort = Dt.Columns[1].ColumnName;

       

        gvXmlToSQL.DataSource = Dt;

        gvXmlToSQL.DataBind();

        Dt.Dispose();

        Da.Dispose();

        cmd.Dispose();

        dbConn.Close();

        //bind GridView from xml DataSource

       // gvXmlToSQL.DataSource = Ds;

       // gvXmlToSQL.DataBind();

    }

}

Here is my .apx page:

<div>

    <asp:GridView ID="gvXmlToSQL" runat="server"></asp:GridView>

    </div>

Here is .xml file

xml version="1.0" encoding="utf-8" ?>

<STUDENTDETAILS>

  <STUDENT id="101">

    <FNAME>Amit</FNAME>

    <LNAME>Singh</LNAME>

    <STD>12th</STD>

    <DIV>B</DIV>

  </STUDENT>

 

  <STUDENT id="102">

    <FNAME>Rajesh</FNAME>

    <LNAME>Mishra</LNAME>

    <STD>12th</STD>

    <DIV>A</DIV>

  </STUDENT>

<STUDENTDETAILS>

Procedure used to get output by passing xml document:

CREATE PROCEDURE usp_GetXMLParameter

@xmlParam XML

AS

BEGIN

SELECT

myTbl.value('(FNAME/Text())[1]','varchar(100)') AS FName,

myTbl.value('(LNAME/Text())[1]','varchar(100)') AS LName,

myTbl.value('(STD/Text())[1]','varchar(10)') AS STD,

myTbl.value('(DIV/Text())[1]','varchar(10)') AS DIV

FROM @xmlParam.nodes('/STUDENTDETAILS/STUDENT') AS tmpTbl(myTbl)

END

Cheers,