View Output
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.
="1.0" ="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.
="1.0" ="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)
{
string strFilePath = Server.MapPath("xml") + @"\studentmaster.xml";
string strFilePathS = Server.MapPath("xml") + @"\resultmaster.xml";
DataSet dsT = new DataSet();
DataSet dsS = new DataSet();
dsT.ReadXml(strFilePath);
dsS.ReadXml(strFilePathS);
string sXML = dsT.GetXml();
string sXMLS = dsS.GetXml();
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
{
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
