SQL PIVOT Table Using OPENXML Query in ASP.NET
View Output
Introduction PIVOT Table & Open XML Query
A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.
You can use the PIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
Syntax
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
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.
XML Shot
We use saleregister.xml in this section and XML available in xml folder in our source to show monthly sale summary.
<?xml version="1.0" encoding="utf-8" ?>
<saleregister>
<saler>
<id>1</id>
<rid>1001</rid>
<name>PUNEET JAIN</name>
<month>jan</month>
<sale>10000</sale>
</saler>
<saler>
<id>2</id>
<rid>1002</rid>
<name>RAKESH MEENA</name>
<month>jan</month>
<sale>8000</sale>
</saler>
<saler>
<id>3</id>
<rid>1003</rid>
<name>PRAKASH MEHTA</name>
<month>jan</month>
<sale>9000</sale>
</saler>
<saler>
<id>4</id>
<rid>1004</rid>
<name>RAJENDRA SINGH</name>
<month>jan</month>
<sale>10000</sale>
</saler>
<saler>
<id>5</id>
<rid>1005</rid>
<name>MOHIT SHARMA</name>
<month>jan</month>
<sale>7000</sale>
</saler>
<saler>
<id>6</id>
<rid>1001</rid>
<name>PUNEET JAIN</name>
<month>feb</month>
<sale>5000</sale>
</saler>
<saler>
<id>7</id>
<rid>1002</rid>
<name>RAKESH MEENA</name>
<month>feb</month>
<sale>6000</sale>
</saler>
<saler>
<id>8</id>
<rid>1003</rid>
<name>PRAKASH MEHTA</name>
<month>feb</month>
<sale>10000</sale>
</saler>
<saler>
<id>9</id>
<rid>1004</rid>
<name>RAJENDRA SINGH</name>
<month>feb</month>
<sale>2000</sale>
</saler>
<saler>
<id>10</id>
<rid>1005</rid>
<name>MOHIT SHARMA</name>
<month>feb</month>
<sale>9000</sale>
</saler>
<saler>
<id>11</id>
<rid>1001</rid>
<name>PUNEET JAIN</name>
<month>mar</month>
<sale>3000</sale>
</saler>
<saler>
<id>12</id>
<rid>1002</rid>
<name>RAKESH MEENA</name>
<month>mar</month>
<sale>8000</sale>
</saler>
<saler>
<id>13</id>
<rid>1003</rid>
<name>PRAKASH MEHTA</name>
<month>mar</month>
<sale>6000</sale>
</saler>
<saler>
<id>14</id>
<rid>1004</rid>
<name>RAJENDRA SINGH</name>
<month>mar</month>
<sale>9000</sale>
</saler>
<saler>
<id>15</id>
<rid>1005</rid>
<name>MOHIT SHARMA</name>
<month>mar</month>
<sale>9000</sale>
</saler>
<saler>
<id>16</id>
<rid>1001</rid>
<name>PUNEET JAIN</name>
<month>apr</month>
<sale>4000</sale>
</saler>
<saler>
<id>17</id>
<rid>1002</rid>
<name>RAKESH MEENA</name>
<month>apr</month>
<sale>5000</sale>
</saler>
<saler>
<id>18</id>
<rid>1003</rid>
<name>PRAKASH MEHTA</name>
<month>apr</month>
<sale>3000</sale>
</saler>
<saler>
<id>19</id>
<rid>1004</rid>
<name>RAJENDRA SINGH</name>
<month>apr</month>
<sale>8000</sale>
</saler>
<saler>
<id>20</id>
<rid>1005</rid>
<name>MOHIT SHARMA</name>
<month>apr</month>
<sale>3000</sale>
</saler>
<saler>
<id>21</id>
<rid>1001</rid>
<name>PUNEET JAIN</name>
<month>may</month>
<sale>3000</sale>
</saler>
<saler>
<id>22</id>
<rid>1002</rid>
<name>RAKESH MEENA</name>
<month>may</month>
<sale>8000</sale>
</saler>
<saler>
<id>23</id>
<rid>1003</rid>
<name>PRAKASH MEHTA</name>
<month>may</month>
<sale>10000</sale>
</saler>
<saler>
<id>24</id>
<rid>1004</rid>
<name>RAJENDRA SINGH</name>
<month>may</month>
<sale>3000</sale>
</saler>
<saler>
<id>25</id>
<rid>1005</rid>
<name>MOHIT SHARMA</name>
<month>may</month>
<sale>5000</sale>
</saler>
</saleregister>
Using the Code
<%@ 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>SQL PIVOT Table Using OPENXML Query in ASP.NET</title>
</head>
<body>
<form id="form1" runat="server">
<table cellpadding="0" cellspacing="0" border="1">
<tr><td colspan="4" align="center"><b>SQL PIVOT Table 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>
<tr><td colspan="4" align="center"><b>Monthly Sale Summary</b></td></tr>
</table>
</form>
</body>
</html>
In page load, firstly we get XML from path and read XML through DataSet then use function ReadXml() to read XML and assign string variable sXML by using GetXml() finally pass to Get_PIVOT_Table_By_OPENXML() function.
// read xml and pass xml to Get_PIVOT_Table_By_OPENXML function.
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (!IsPostBack)
{
string strFilePath = Server.MapPath("xml") + @"\saleregister.xml";
DataSet dsT = new DataSet();
dsT.ReadXml(strFilePath);
string sXML = dsT.GetXml();
Get_PIVOT_Table_By_OPENXML(sXML);
}
}
catch (Exception ex) { }
}
Function Get_PIVOT_Table_By_OPENXML() receives xml and completes the query to make PIVOT table using function RunSqlQuery() and fill records in gridview.
// function Get_PIVOT_Table_By_OPENXML to get PIVOT Table
protected void Get_PIVOT_Table_By_OPENXML(string sXML)
{
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 * FROM OPENXML (@handle, 'saleregister/saler', 2) " +
"WITH (rid INT,name VARCHAR(20),month VARCHAR(5),
sale INT) " + // openxml column field datatype
"TempTable "+
"PIVOT "+
"( "+
"SUM(sale) "+ //pivot aggregation function
"FOR MONTH "+ //pivot column field name
"IN (jan,feb,mar,apr,may) "+ //pivot collumn field value
") PivotTable ORDER BY rid "+
"EXEC sp_xml_removedocument @handle ";
if (RunSqlQuery(sQuery, out dsTemp))
{ gvRecord.DataSource = dsTemp.Tables[0]; }
gvRecord.DataBind();
}
catch (Exception ex) { }
}
Function RunSqlQuery() runs query, returns bool value true on success and out DataSet.
// function RunSqlQuery to run query.
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;
}
Points of Interest
Microsoft SQL provides good features to handle XML using Open XML query, and also provides PIVOT table features to generate pivot report for data analysis. In this post, we try to explain both features.
"发表评论
cbd oil washington d[89.28.10.*]2020/11/19 1:24:12#5tIakZF Thanks-a-mundo for the blog article.Thanks Again. Keep writing.
exiwvf Major thankies for the article post. Really Cool.
3yUOzL Very good post.Really looking forward to read more.
rP5bTb It is in fact a cool and beneficial piece of details. I am content which you just shared this practical information and facts with us. Please retain us informed like this. Thank you for sharing.
Y3A3xY Really informative post.Much thanks again. Fantastic.
