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

SQL PIVOT Table Using OPENXML Query in ASP.NET

, 11 May 2011
Rate this:
Please Sign up or sign in to vote.
Using Open XML Query and generating PIVOT report

View Output

Output Image

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.

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

 
GeneralMy vote of 4 PinmemberDeepankar Ranjan Dey29-Mar-14 20:23 
QuestionCreating Word document from SQL Query [using For XML] Pinmemberjusticet22-Jul-11 3:46 
Herewith the code, I am pushing XML data from SQL Query to VB.Net and then create a word document from the XML data. The data is saved in the SQL Table.
 
The document creates well, but my problem is that when i try to load the XSLT to the data received the Word document does not show the data. Please assist on how can get the XSLT work on data received from SQL For XML.
 

Partial Class Form1
 
Inherits System.Web.UI.Page
 
Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
 

Dim sqlConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim command As SqlCommand = _
New SqlCommand("sTest", sqlConn)
command.CommandType = CommandType.StoredProcedure
 

Dim ds As DataSet
Dim xmlDoc As XmlDataDocument
Dim xslTran As XslCompiledTransform
Dim root As XmlElement
Dim nav As XPathNavigator
Dim writer As XmlTextWriter
 
Dim doc As New XmlDocument()
Dim da As New SqlDataAdapter

 
Try

''fill dataset with sql table
sqlConn.Open()
 
ds = New DataSet()
 

da.SelectCommand = New SqlCommand("sTest", sqlConn)
da.Fill(ds, "Persons")
 
doc.LoadXml(ds.GetXml())
 
sqlConn.Close()
sqlConn.Dispose()
 

'Determine the Root object in the XML
root = doc.DocumentElement
 
'Create the XPath Navigator to navigate throuth the XML
nav = root.CreateNavigator()
 
'First delete the RTF, if already exist
If File.Exists("People.doc") Then
File.Delete("People.doc")
End If
 
'Create the RTF/doc by Transforming the XML and XSLT
writer = New XmlTextWriter("c:\People.doc", System.Text.Encoding.[Default])
 
'Load the XSLT for Transformation
xslTran = New XslCompiledTransform()
xslTran.Load("People.xslt")
 
xslTran.Transform(nav, writer)
 
'Close the Writer after Transformation
writer.Close()
 
Response.Write("Document created successfully.....")
 
Catch ex As Exception
Response.Write(ex.StackTrace)
End Try
End Sub
End Class
 

 
Here with the SQL Query:
 
I created a procedure that only selects data from a table::
 
select [id],[firstname]
from people.dbo.persons
for xml raw('persons'), Root('People'), elelments;
 
Herewith my XSLT:
 
Everywhere where you see < (suppose to be <) and > (suppose to be >)
 

<!-- Edited by XMLSpy® -->
GeneralMy vote of 5 PinmemberAnurag Gandhi12-May-11 20:43 

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
Web04 | 2.8.140721.1 | Last Updated 11 May 2011
Article Copyright 2011 by R S Dodiya
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid