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
关键字 compare,two,xml,by,using,openxml,query,in,asp.net
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架