Click here to Skip to main content
Click here to Skip to main content

Compare Two XML By Using OPENXML Query in ASP.NET

By , 18 May 2011
Rate this:
Please Sign up or sign in to vote.

View Output

Output Image

Introduction

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. For more details, click here.

XML Block

studentmaster.xml is available in the xml folder in our source code.

<?xml version="1.0" encoding="utf-8" ?>
<resultmaster>
  <result rid="1001" hindi="45" english="75" maths="55"
  	total="175" percentage="58.33" division="second" />
  <result rid="1002" hindi="65" english="65" maths="90"
  	total="220" percentage="73.33" division="first" />
  <result rid="1003" hindi="36" english="65" maths="75"
  	total="176" percentage="58.66" division="second" />
  <result rid="1004" hindi="80" english="85" maths="65"
  	total="230" percentage="76.66" division="first" />
  <result rid="1005" hindi="75" english="55" maths="55"
  	total="185" percentage="61.66" division="first" />
</resultmaster>

resultmaster.xml is available in the xml folder in our source code.

<?xml version="1.0" encoding="utf-8" ?>
<resultmaster>
  <result rid="1001" hindi="45" english="75" maths="55"
  	total="175" percentage="58.33" division="second" />
  <result rid="1002" hindi="65" english="65" maths="90"
  	total="220" percentage="73.33" division="first" />
  <result rid="1003" hindi="36" english="65" maths="75"
  	total="176" percentage="58.66" division="second" />
  <result rid="1004" hindi="80" english="85" maths="65"
 	total="230" percentage="76.66" division="first" />
  <result rid="1005" hindi="75" english="55" maths="55"
  	total="185" percentage="61.66" division="first" />
</resultmaster>

HTML Code Block

<%@ 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>Compare Two XML By Using OPENXML Query</title>
</head>
<body>
<form id="form1" runat="server">
<table cellpadding="0" cellspacing="0" border="1">
<tr><td colspan="4" align="center"><b>
Compare Two XML By Using OPENXML Query</b></td></tr>
<tr><td colspan="4">
<asp:GridView ID="gvRecord" runat="server" AutoGenerateColumns="true"
HeaderStyle-BackColor="Gray" HeaderStyle-ForeColor="White" />
</td></tr>
</table>
</form>
</body>
</html>

SQL Generate Query

You can use the SQL generated query directly in Microsoft SQL Server. For this, just copy and paste in SQL Server query window and run.

DECLARE @handle INT
DECLARE @xD XML
SET @xD='<studentmaster>
<student id="1001" name="PUNEET JAIN" age="16" />
<student id="1002" name="RAKESH MEENA" age="15" />
<student id="1003" name="PRAKASH MEHTA" age="17" />
<student id="1004" name="RAJENDRA SINGH" age="16" />
<student id="1005" name="MOHIT SHARMA" age="15" />
</studentmaster>'
EXEC sp_xml_preparedocument @handle OUTPUT, @xD
SELECT * INTO [#MyTablePrimery] FROM OPENXML (@handle, 'studentmaster/student', 1)
WITH (id INT,name VARCHAR(20),age INT)
ORDER BY [id]
EXEC sp_xml_removedocument @handle
SET @xD='<resultmaster>
<result rid="1001" hindi="45" english="75" maths="55"
	total="175" percentage="58.33" division="second" />
<result rid="1002" hindi="65" english="65" maths="90"
	total="220" percentage="73.33" division="first" />
<result rid="1003" hindi="36" english="65" maths="75"
	total="176" percentage="58.66" division="second" />
<result rid="1004" hindi="80" english="85" maths="65"
	total="230" percentage="76.66" division="first" />
<result rid="1005" hindi="75" english="55" maths="55"
	total="185" percentage="61.66" division="first" />
</resultmaster>'
EXEC sp_xml_preparedocument @handle OUTPUT, @xD
SELECT * INTO [#MyTableSecondary] FROM OPENXML (@handle, 'resultmaster/result', 1)
WITH (rid INT,hindi INT,english INT,maths INT,total INT,
	percentage FLOAT,division VARCHAR(15))
ORDER BY [rid]
EXEC sp_xml_removedocument @handle
SELECT * FROM [#MyTablePrimery],[#MyTableSecondary]
WHERE [id]=[rid]
DROP TABLE [#MyTablePrimery]
DROP TABLE [#MyTableSecondary] 

C# Code Block

Read both XML and pass into this Compare2XML_By_OPENXML() function to generate query. Both XML Select query results are stored in SQL temporary tables with names #MyTablePrimary and #MyTableSecondary. Next, again write normal SQL query to get results from both temporary tables. Finally, use DROP TABLE to remove both temporary tables.

protected void Page_Load(object sender, EventArgs e)
{
    try
    {
        if (!IsPostBack)
        {
	   // get xml path by using Server.MapPath() function.
            string strFilePath = Server.MapPath("xml") + @"\studentmaster.xml";
            string strFilePathS = Server.MapPath("xml") + @"\resultmaster.xml";
	   // Create new Dataset and read xml finally assign into string variable.
            DataSet dsT = new DataSet();
            DataSet dsS = new DataSet();
            dsT.ReadXml(strFilePath);
            dsS.ReadXml(strFilePathS);
            string sXML = dsT.GetXml();
            string sXMLS = dsS.GetXml();
	   // call this function and pass both string xml variable.
            Compare2XML_By_OPENXML(sXML, sXMLS);
        }
    }
    catch (Exception ex) { }
}
protected void Compare2XML_By_OPENXML(string sXML,string sXMLS)
{
    string sQuery = string.Empty;
    DataSet dsTemp = null; gvRecord.DataSource = null;
    try
    {
        // generating dynamic OPENXML query
        sQuery = "DECLARE @handle INT " +
                 "DECLARE @xD XML " +
                 "SET @xD='" + sXML + "' " +
                 "EXEC sp_xml_preparedocument @handle OUTPUT, @xD " +
                 "SELECT * INTO [#MyTablePrimery] FROM OPENXML 
			(@handle, 'studentmaster/student', 1) " +
                 "WITH (id INT,name VARCHAR(20),age INT) " +
                 "ORDER BY [id] "+
                 "EXEC sp_xml_removedocument @handle ";
        sQuery +="SET @xD='" + sXMLS + "' " +
                 "EXEC sp_xml_preparedocument @handle OUTPUT, @xD " +
                 "SELECT * INTO [#MyTableSecondary] FROM OPENXML 
			(@handle, 'resultmaster/result', 1) " +
                 "WITH (rid INT,hindi INT,english INT,maths INT,
                 total INT,percentage FLOAT,division VARCHAR(15)) " +
                 "ORDER BY [rid] " +
                 "EXEC sp_xml_removedocument @handle ";
        sQuery += "SELECT * FROM [#MyTablePrimery],[#MyTableSecondary] 
			WHERE [id]=[rid] " +
                  "DROP TABLE [#MyTablePrimery] " +
                   "DROP TABLE [#MyTableSecondary] ";

        if (RunSqlQuery(sQuery, out dsTemp))
        { gvRecord.DataSource = dsTemp.Tables[0]; }
        gvRecord.DataBind();
    }
    catch (Exception ex) {  }
}

History

  • 17th May, 2011: Initial version

License

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

About the Author

R S Dodiya
Software Developer (Senior)
India India

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 18 May 2011
Article Copyright 2011 by R S Dodiya
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid