(untagged)

SQL PIVOT Table Using OPENXML Query in ASP.NET

R S Dodiya

0.00/5 (No votes)

11 May 2011

Using Open XML Query and generating PIVOT report

Download source - 10.15 KB

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here