Click here to Skip to main content

Grouping XML using XSLT

<!-- Download Links --> <!-- Add the rest of your HTML here -->

Introduction

Processing a list of XML elements using XSLT is fairly simple if you want to process each element. But what if you want to group the XML elements, to show a summary? Consider the following XML:

<?xml version="1.0" ?>
<Employees>
 <Employee>
  <TeamID>1</TeamID>
  <TeamName>Sales</TeamName>
  <TaskID>1</TaskID>
  <Hours>5</Hours>
  <EmployeeID>1</EmployeeID>
  <Name>Bob</Name>
  <Surname>Shibob</Surname>
 </Employee>
 <Employee>
  <TeamID>1</TeamID>
  <TeamName>Sales</TeamName>
  <TaskID>2</TaskID>
  <Hours>4</Hours>
  <EmployeeID>1</EmployeeID>
  <Name>Bob</Name>
  <Surname>Shibob</Surname>
 </Employee>
 <Employee>
  <TeamID>1</TeamID>
  <TeamName>Sales</TeamName>
  <TaskID>4</TaskID>
  <Hours>7</Hours>
  <EmployeeID>2</EmployeeID>
  <Name>Sara</Name>
  <Surname>Lee</Surname>
 </Employee>
 <Employee>
  <TeamID>2</TeamID>
  <TeamName>Finance</TeamName>
  <TaskID>5</TaskID>
  <Hours>2</Hours>
  <EmployeeID>3</EmployeeID>
  <Name>John</Name>
  <Surname>Smith</Surname>
 </Employee>
 <Employee>
  <TeamID>2</TeamID>
  <TeamName>Finance</TeamName>
  <TaskID>3</TaskID>
  <Hours>4</Hours>
  <EmployeeID>4</EmployeeID>
  <Name>Penny</Name>
  <Surname>Wise</Surname>
 </Employee>
 <Employee>
  <TeamID>2</TeamID>
  <TeamName>Finance</TeamName>
  <TaskID>5</TaskID>
  <Hours>3</Hours>
  <EmployeeID>4</EmployeeID>
  <Name>Penny</Name>
  <Surname>Wise</Surname>
 </Employee>
</Employees>

Suppose that you need to show a summary of Employee hours, grouped by Team. Something like this:

<!-- Article image -->

Sample Image - groupxml.jpg

The unwieldy approach

One way to do this is to loop through the list of <Employee> elements, and only show a row whenever the EmployeeID changes. While this would work, this approach is unwieldy and inefficient, because for each <Employee> being processed, you would be required to keep track of the IDs of the previous <Employee> element. This is not a pretty sight.

The efficient approach

A cleaner, more efficient way to do this is to build a list of unique keys, then use these keys to group the results. (This is called the Muenchian Method.)

First, you must define the keys required to group the <Employee> elements. You will need one for the TeamID, and one for the EmployeeID.

<xsl:key <code>name="keyTeamID"</code> match="Employee" <code>use="TeamID"</code> />
<xsl:key <code>name="keyEmployeeID"</code> match="Employee" <code>use="EmployeeID"</code> />

Select the first element of each group of elements for each unique TeamID.

<xsl:for-each select="//<code>Employee[generate-id(.) = generate-id(key('keyTeamID', TeamID)[1]</code>)]">

Get all the <Employee> elements that belong to that Team, into a variable.

<!-- Save the ID of the Team to a variable -->
<xsl:variable name="lngTeamID"><xsl:value-of select="TeamID" /></xsl:variable>
<!-- Select all the Employees belonging to the Team -->
<xsl:variable name="lstEmployee" select="<code>//Employee[TeamID=$lngTeamID]</code>" />

The <Employee> elements in this list must now be grouped by EmployeeID. This is similar to grouping by TeamID, except that in this case you only need to select elements in the list contained in the variable; you do not need to select elements from the entire result set.

<xsl:for-each select="<code>$lstEmployee</code>[generate-id(.) = generate-id(key(<code>'keyEmployeeID', EmployeeID</code>)[1])]">

It is now fairly simple to show the total Hours for each Employee.

<xsl:value-of select="sum(<code>$lstEmployee[EmployeeID=$lngEmployeeID]/Hours</code>)" />

The full source

This is the entire XSLT used to render the table in the image:

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" 
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<!-- Define keys used to group elements -->
<code><xsl:key name="keyTeamID" match="Employee" use="TeamID" />
<xsl:key name="keyEmployeeID" match="Employee" use="EmployeeID" /></code>

<xsl:template match="/">
 <html>
  <head>
   <title>Employee Hours By Team</title>
   <link type="text/css" rel="stylesheet" href="groupxml.css" />
  </head>
  <body>
   <h3>Employee Hours By Team</h3>
   <table>
   
    <!-- Process each Team -->
    <code><xsl:for-each select="//Employee[generate-id(.) = generate-id(key('keyTeamID', TeamID)[1])]"></code>
     <xsl:variable name="lngTeamID"><xsl:value-of select="TeamID" /></xsl:variable>
     <!-- Select all the Employees belonging to the Team -->
     <code><xsl:variable name="lstEmployee" select="//Employee[TeamID=$lngTeamID]" /></code>
     <!-- Show details for Employees in Team -->
     <xsl:call-template name="ShowEmployeesInTeam">
      <code><xsl:with-param name="lstEmployee" select="$lstEmployee" /></code>
     </xsl:call-template>
    </xsl:for-each>
   
    <tr>
     <td colspan="4" class="RightJustified DarkBack">Grand Total</td>
     <td colspan="1" class="RightJustified DarkBack">
      <!-- Show Grand Total of hours for all Employees -->
      <xsl:value-of select="sum(//Employee/Hours)" />
     </td>
    </tr>
   </table>
  </body>
 </html>
</xsl:template>

<xsl:template name="ShowEmployeesInTeam">
 <code><xsl:param name="lstEmployee" /></code>
 
 <!-- Show the name of the Team currently being processed -->
 <tr>
  <td colspan="4" class="DarkBack">TEAM: <xsl:value-of select="<code>$lstEmployee[1]/TeamName</code>" /></td>
  <td colspan="1" class="DarkBack RightJustified">HOURS</td>
 </tr>
 
 <!-- Show the total hours for each Employee in the Team -->
 <code><xsl:for-each select="$lstEmployee[generate-id(.) = generate-id(key('keyEmployeeID', EmployeeID)[1])]"></code>
  <xsl:variable name="lngEmployeeID" select="EmployeeID" />
  <!-- Show details of each Employee -->
  <tr>
   <td colspan="4">
    <xsl:value-of select="$lstEmployee[EmployeeID=$lngEmployeeID]/Name" />
     <xsl:value-of select="$lstEmployee[EmployeeID=$lngEmployeeID]/Surname" />
   </td>
   <td colspan="1" class="RightJustified">
    <!-- Show the total hours for the current Employee -->
    <code><xsl:value-of select="sum($lstEmployee[EmployeeID=$lngEmployeeID]/Hours)" /></code>
   </td>
  </tr>
 </xsl:for-each>
 
 <tr>
  <td colspan="4" class="LightBack RightJustified">Sub-Total</td>
  <td colspan="1" class="LightBack RightJustified">
   <!-- Show the total hours for all Employees in the Team -->
   <xsl:value-of select="sum($lstEmployee/Hours)" />
  </td>
 </tr>
</xsl:template>

</xsl:stylesheet>

The CSS used to render the table in the image:

table
{  border-collapse: collapse;
   width: 30%;
   table-layout: fixed;
   border-style: solid;
}
table, td
{  border-width: 1px;
}
td
{  color: black;
   font-family: Arial;
   font-size: x-small;
   border-right-style: none;
   border-left-style: none;
   border-top-style: solid;
   border-bottom-style: solid;
}
.DarkBack
{  background-color: #0066FF;
   background-color: blue;
   color: white;
   font-weight: bold;
}
.LightBack
{  background-color: #99CCFF;
   color: black;
}
.RightJustified
{  text-align: right;
}

Web04 | 2.8.160207.1 | Advertise | Privacy
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service