Click here to Skip to main content
Click here to Skip to main content
Go to top

Handling XML with OPENXML

, 20 Jan 2011
Rate this:
Please Sign up or sign in to vote.
By using OPENXML, handling XML is very easy. We use OPENXML to get XML records in table format (rows and column)

Introduction

In ASP.NET development, we normally use XML in our programming. So in this example, we are using XML, and handle XML by Querying XML Using OPENXML.

About OPENXML (Transact-SQL keyword)

OPENXML allows access to XML data as though it is a relational rowset. It does this by providing a rowset view of the internal representation of an XML document. The records in the rowset can be stored in database tables.
To write queries against an XML document by using OPENXML, you must first call sp_xml_preparedocument. This parses the XML document and returns a handle to the parsed document that is ready for consumption. The parsed document is a document object model (DOM) tree representation of various nodes in the XML document. The document handle is passed to OPENXML. OPENXML then provides a rowset view of the document, based on the parameters passed to it.
A parsed document is stored in the internal cache of SQL Server, so to avoid running out of memory, run sp_xml_removedocument to free up the memory.

Syntax:

OPENXML( idoc int [ in] ,rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) 
[ WITH ( SchemaDeclaration | TableName ) ]
idoc
Is the document handle of the internal representation of an XML document. The internal representation of an XML document is created by calling sp_xml_preparedocument.
rowpattern
Is the XPath pattern used to identify the nodes (in the XML document whose handle is passed in the idoc parameter) to be processed as rows.
flags
Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. flags is an optional input parameter.
Here we are using flag value “2”. The element-centric mapping. Can be combined with XML_ATTRIBUTES. In this case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.
The WITH clause provides a rowset format (and additional mapping information as required) by using either SchemaDeclaration or specifying an existing TableName.

Follow Steps

  1. Open Visual Studio, create blank solution and add new website.
  2. Create xml folder in your website root.(folder name is "xml")
  3. Paste HTML code in your form tag of default.aspx page.
  4. Paste all .cs code in default.aspx.cs page, Add require namespac also.
  5. Compile and run.

XML File

Our XML file path is:

string strFilePath = Server.MapPath("xml") + @"\officeemployee.xml";
<?xml version="1.0" standalone="yes"?>
<DocumentElement>
  <employee>
    <id>1</id>
    <name>rajendra</name>
    <age>23</age>
    <salary>2500</salary>
  </employee>
  <employee>
    <id>2</id>
    <name>mukesh</name>
    <age>25</age>
    <salary>3000</salary>
  </employee>
</DocumentElement>

HTML Code

Past below HTML code in form tag of default.aspx in ASP.NET.

<table cellpadding="0" cellspacing="0" border="1">
<tr><td colspan="4" align="center">Handle XML By SQL Query</td></tr>
<tr style="background-color:Gold;font-weight:bold;"><td align="center">NAME</td><td align="center">AGE</td><td align="center">SALARY</td><td>RECORD</td></tr>
<tr>
<td align="center"><asp:TextBox ID="txtName" runat="server" Width="100px"/></td>
<td align="center"><asp:TextBox ID="txtAge" runat="server" Width="100px" Text="0"/></td>
<td align="center"><asp:TextBox ID="txtSalary" runat="server" Width="100px" Text="0.0"/></td>
<td align="center"><asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" /></td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"></td>
<td align="center"></td>
<td align="center"></td>
</tr>
<tr>
<td align="center"><asp:DropDownList ID="ddlFindBy" runat="server" Width="100px"/></td>
<td align="center"><asp:TextBox ID="txtFindBy" runat="server" Width="100px"/></td>
<td align="center"><asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" /></td>
<td align="center"><asp:LinkButton ID="lnkFillRecord" runat="server" Text="Get All" OnClick="lnkFillRecord_Click" /></td>
</tr>
<tr><td colspan="4"> </td></tr>
<tr><td colspan="4">
<asp:GridView ID="gvRecord" runat="server" AutoGenerateColumns="false" Width="100%" HeaderStyle-BackColor="Gray" HeaderStyle-ForeColor="White" >
<columns>
<asp:TemplateField HeaderText="ID">
<itemtemplate><%#Eval("id")%></itemtemplate>
 
<asp:TemplateField HeaderText="NAME">
<itemtemplate><%#Eval("name")%></itemtemplate>
 
<asp:TemplateField HeaderText="AGE">
<itemtemplate><%#Eval("age")%></itemtemplate>
 
<asp:TemplateField HeaderText="SALARY">
<itemtemplate><%#Eval("salary")%></itemtemplate>
 
<asp:TemplateField HeaderText="DELETE">
<%--Bind DataTable Row index to operate record.--%>
<itemtemplate><asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" ToolTip='<%#Eval("id")%>' OnClick="lnkDelete_Click" /></itemtemplate>
<itemstyle horizontalalign="Center" />
 
</columns>
 
</td></tr>
</table>
 
<asp:Label ID="lblMsg" runat="server" Text="0" Font-Bold="true" ForeColor="#ff0000"/>
 

.CS Code

Create folder in your project name "xml", to save XML. Add namespace System.Data.SqlClient;for SQL Server.

 
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!IsPostBack)
            {
                FillSearch();
            }
        }
        catch (Exception ex) { }
    }
    # region ButtonEventRegion
    protected void btnSave_Click(object sender, EventArgs e)
    {
        AddRecordInXML();
    }
    protected void lnkDelete_Click(object sender, EventArgs e)
    {
        string sRecordId = ((LinkButton)(sender)).ToolTip;
        DeleteRecordInXML(sRecordId);
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        FillGridBySearch();
    }
    protected void lnkFillRecord_Click(object sender, EventArgs e)
    {
        FillDataGrid();
    }
    # endregion

Function Region

In Function Access region belong to general function to operate XML operation. AddRecordInXML() function the first find the xml in root xml folder, if not found then call CreateXML() method. If find then call GetMaxId() method to get Last Id of Record and save new record with increase Id by one and call SaveXMLRecord() function. The main point is here we use OPENXML scalar query in our GetMaxId() method.
    protected void AddRecordInXML()
    {
        try
        {
            string strFilePath = Server.MapPath("xml") + @"\officeemployee.xml";
            if (System.IO.File.Exists(strFilePath))
            {
                DataSet dsT = new DataSet();
                dsT.ReadXml(strFilePath);
                string sXML = dsT.GetXml();
                int iId = 0;
                if (GetMaxId(sXML, "DocumentElement/employee", out iId))
                {
                    iId = iId + 1;
                    SaveXMLRecord(iId.ToString(), txtName.Text, txtAge.Text, txtSalary.Text);
                    lblMsg.Text = iId.ToString();
                }
                else
                { CreateXML("1", txtName.Text, txtAge.Text, txtSalary.Text); }
            }
            else
            { CreateXML("1", txtName.Text, txtAge.Text, txtSalary.Text); }
        }
        catch (Exception ex)
        { }
    }
    protected bool GetMaxId(string sXML, string sLevel, out int iId)
    {
      bool retVal = false; string sQuery = string.Empty;
      int iTemp = 0;
      try
      {
        sQuery = "DECLARE @handle INT " +
                 "DECLARE @xD XML " +
                 "SET @xD='" + sXML + "' " +
                 "EXEC sp_xml_preparedocument @handle OUTPUT, @xD " +
                 "SELECT MAX(id) AS [id] FROM OPENXML (@handle, '" + sLevel + "', 2) " +
                 "WITH (id INT) " +
                 "EXEC sp_xml_removedocument @handle ";
        if (RunSqlScalarQuery(sQuery, out iTemp))
        { retVal = true; }
      }
      catch (Exception ex) { }
      iId = iTemp;
      return retVal;
    }
DeleteRecordInXML() function get xml dataset and delete record from datatable by id, and call finally AcceptChanges() for DataTable to save or update changes in datatable and write again xml in root xml folder. GetRecordByFind() is our next main function of use OPENXML query.
 
    protected void DeleteRecordInXML(string sRecordId)
    {
        try
        {
            string sId = string.Empty;
            string strFilePath = Server.MapPath("xml") + @"\officeemployee.xml";
            DataSet dsTemp = new DataSet();
            dsTemp.ReadXml(strFilePath);
            DataRowCollection drcT = dsTemp.Tables[0].Rows;
            foreach (DataRow drT in drcT)
            { if (sRecordId == drT["id"].ToString()) { drT.Delete(); break; } }
            dsTemp.Tables[0].AcceptChanges();
            dsTemp.WriteXml(strFilePath);
            lblMsg.Text = "Row is deleted";
            FillDataGrid();
        }
        catch (Exception ex) { }
    }
 
    protected bool GetRecordsByFind(string sSearch, string sXML, string sLevel, out DataSet dsT)
    {
        bool retVal = false; string sQuery = string.Empty;
        DataSet dsTemp = null;
        try
        {
            sQuery = "DECLARE @handle INT " +
                     "DECLARE @xD XML " +
                     "SET @xD='" + sXML + "' " +
                     "EXEC sp_xml_preparedocument @handle OUTPUT, @xD " +
                     "SELECT * FROM OPENXML (@handle, '" + sLevel + "', 2) " +
                     "WITH (id INT,name VARCHAR(15),age int,salary REAL) " +
                     "WHERE " + sSearch + " " +
                     "EXEC sp_xml_removedocument @handle ";
            if (RunSqlQuery(sQuery, out dsTemp))
            { retVal = true; }
        }
        catch (Exception ex) {  }
        dsT = dsTemp;
        return retVal;
    }
 
FillSearch() function for adding search field in DropDownList object, this search field according to our xml structure. FillDataGrid() function get all record from xml and FillGridBySearch() work according to search selection.
 
 
    protected void FillSearch()
    {
        try
        {
            ddlFindBy.Items.Add(new ListItem("Id", "id"));
            ddlFindBy.Items.Add(new ListItem("Name", "name"));
            ddlFindBy.Items.Add(new ListItem("Age", "age"));
            ddlFindBy.Items.Add(new ListItem("Salary", "salary"));
        }
        catch (Exception ex) { }
    }
    protected void FillDataGrid()
    {
      try
      {
        string strFilePath = Server.MapPath("xml") + @"\officeemployee.xml";
        DataSet dsTemp = new DataSet();
        dsTemp.ReadXml(strFilePath);
        if (dsTemp.Tables.Count &gt; 0) { gvRecord.DataSource = dsTemp.Tables[0]; }
        else { gvRecord.DataSource = null; }
        gvRecord.DataBind();
      }
      catch (Exception ex) {  }
    }
 
    protected void FillGridBySearch()
    {
        try
        {
            string strFilePath = Server.MapPath("xml") + @"\officeemployee.xml";
            string sSearch = ddlFindBy.SelectedItem.Value + " LIKE '" + txtFindBy.Text.Trim().Replace("'", "") + "%'";
            DataSet dsTemp = new DataSet();
            dsTemp.ReadXml(strFilePath);
            string sXML = dsTemp.GetXml();
            dsTemp = null; gvRecord.DataSource = null;
            if (GetRecordsByFind(sSearch, sXML, "DocumentElement/employee", out dsTemp))
            { gvRecord.DataSource = dsTemp.Tables[0]; }
            gvRecord.DataBind();
        }
        catch (Exception ex) { }
    }
 
CrateXML() function is simply create a structure of DataTable which we want to use as xml structure, then add new row value in datatable and call WriteXML() method of DataSet to create XML and save in root xml folder.
 
    protected void CreateXML(string  sId,string sName,string sAge,string sSalary)
    {
        try
        {
            DataTable dtT = new DataTable("employee");
            DataColumn myDataColumn = null;
            myDataColumn = new DataColumn("id", Type.GetType("System.Int32"));
            dtT.Columns.Add(myDataColumn);
            myDataColumn = new DataColumn("name", Type.GetType("System.String"));
            dtT.Columns.Add(myDataColumn);
            myDataColumn = new DataColumn("age", Type.GetType("System.Byte"));
            dtT.Columns.Add(myDataColumn);
            myDataColumn = new DataColumn("salary", Type.GetType("System.Single"));
            dtT.Columns.Add(myDataColumn);
            DataRow drT = dtT.NewRow();
            drT["id"] = sId;
            drT["name"] = sName;
            drT["age"] = sAge;
            drT["salary"] = sSalary;
            dtT.Rows.Add(drT);
            string strFilePath = Server.MapPath("xml") + @"\officeemployee.xml";
            dtT.WriteXml(strFilePath);
            FillDataGrid();
        }
        catch (Exception ex) { }
    }
 
SaveXMLRecord() function get xml from root/xml/ folder and read xml fill in dataset, to get new row from datatable and add to datatable as new record, and call function WriteXml() to save xml. Call FillDataGrid() to view added record.
 
    protected void SaveXMLRecord(string sId, string sName, string sAge, string sSalary)
    {
        try
        {
            string strFilePath = Server.MapPath("xml") + @"\officeemployee.xml";
            DataSet dsTemp = new DataSet();
            dsTemp.ReadXml(strFilePath);
            DataRow drT = dsTemp.Tables[0].NewRow();
            drT["id"] = sId;
            drT["name"] = sName;
            drT["age"] = sAge;
            drT["salary"] = sSalary;
            dsTemp.Tables[0].Rows.Add(drT);
            dsTemp.WriteXml(strFilePath);
            FillDataGrid();
        }
        catch (Exception ex) { }
    }

Data Access Region

In Data Accress region, create two functions RunSqlQuery and RunSqlScalarQuery. In our connection @"Data Source=.\SQLEXPRESS;Initial Catalog=;Integrated Security=True", the Initial Catalog name is empty, because here we are using XML.

 
   #region DataAccessRegion
    
    protected bool RunSqlQuery(string sQuery, out DataSet dsRecords)
    {
        bool retVal = false;
        DataSet dsTemp = null;
        SqlConnection sCon = null;
        try
        {
            string sConStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=;Integrated Security=True";
            sCon = new SqlConnection(sConStr);
            SqlCommand sCmd = new SqlCommand(sQuery, sCon);
            sCmd.CommandType = CommandType.Text;
            SqlDataAdapter sAdp = new SqlDataAdapter(sCmd);
            dsTemp = new DataSet();
            sCon.Open();
            sAdp.Fill(dsTemp);
            if (dsTemp.Tables[0].Rows.Count > 0) { retVal = true; }
        }
        catch (Exception ex) { }
        finally { sCon.Close(); }
        dsRecords = dsTemp;
        return retVal;
    }
 
    protected bool RunSqlScalarQuery(string sQuery, out int iId)
    {
        bool retVal = false;
        int iTemp = 0; string sResult = string.Empty;
        SqlConnection sCon = null;
        try
        {
            string sConStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=;Integrated Security=True";
            sCon = new SqlConnection(sConStr);
            SqlCommand sCmd = new SqlCommand(sQuery, sCon);
            sCmd.CommandType = CommandType.Text;
            sCon.Open();
            iTemp = (int)sCmd.ExecuteScalar();
            if (iTemp > 0) { retVal = true; }
        }
        catch (Exception ex) { }
        finally { sCon.Close(); }
        iId = iTemp;
        return retVal;
    }
    #endregion

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

R S Dodiya
Software Developer (Senior)
India India

Comments and Discussions

 
GeneralThe OpenXML approach in general is a good one to take. Abou... Pinmembertorial21-Jan-11 4:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 21 Jan 2011
Article Copyright 2011 by R S Dodiya
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid