Click here to Skip to main content
Click here to Skip to main content
Search within:



 
Comments & Discussions (20)

Grouping XML using XSLT

By , 17 Feb 2002
 

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:

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;
}

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

About the Author

 MS le Roux
Web Developer
South Africa South Africa
Member
I live in the Northern Suburbs of Cape Town (South Africa).

Article Top
 
Sign Up to vote   Poor Excellent
Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

 
You must Sign In to use this message board.
Search this forum  
    Spacing  Noise  Layout  Per page   
First PrevNext
GeneralMy vote of 4memberVarunKumarGB14 Dec '10 - 22:23 
Very nicely written!
Sign In·View Thread·Permalink
GeneralMy vote of 3memberlijo011@yahoo.com15 Aug '10 - 23:57 
this helped me a good starting for XSLT grouping
Sign In·View Thread·Permalink
GeneralGrouping XML datamemberblindcapt31 Mar '09 - 18:15 
Hi,
I have a similar issue to the one in your article that I'm trying to solve. I thought it would be easy but I wonder now after looking at your article.
 
Can you give me a suggestion for this problem:
 
If you can help me write an XSLT to generate the one from the other, I would be greatly appreciative. It shouldn’t be too difficult, the only problem I’m having is in the looping process that nests the records properly inside their category.
 
The original (start) structure looks like this:
 
<Inventory>
<Item>
<producttype>Book</producttype>
<productname>A Connecticut Yankee in King Arthur's Court</productname>
<artistfirstname>Mark</artistfirstname>
<artistlastname>Twain</artistlastname>
<description>A Connecticut Yankee travels back to King Arthur's court and adventure ensues. Fun, fun, fun.</description>
<company>Pocket Books</company>
<year>1889</year>
<productid>978-1416534730</productid>
<price>4.95</price>
<image/>
</Item>
</Inventory>
 
The target (final) structure should look like this:
 
<Inventory>
<category>NEW TAG
<type>Book</type>NEW TAG. SELECTS PRODUCTS BY <PRODUCTTYPE> AND NESTS THEM WITHIN NEW TAG: <CATEGORY>
<Item>SELECTS ITEM DATA AND RE-INSERTS IT BY <PRODUCTTYPE>. ALL TAGS ARE INCLUDED EXCEPT <PRODUCTTYPE>
<productname>A Connecticut Yankee in King Arthur's Court</productname>
<artistfirstname>Mark</artistfirstname>
<artistlastname>Twain</artistlastname>
<description>A Connecticut Yankee travels back to King Arthur's court and adventure ensues. Fun, fun, fun.</description>
<company>Pocket Books</company>
<year>1889</year>
<productid>978-1416534730</productid>
<price>4.95</price>
<image/>
</Item>
</category>
</Inventory>

 
Any help you can provide would be great!
 
Thanks
 
Jim
Sign In·View Thread·Permalink
GeneralPerformance issuememberQing Jiang30 Mar '06 - 3:59 
MS le:
 
Your way will be very slow when you deal with big xml file, I say big I mean the number of records is more than 500.
 
The better way to handle grouping is to do it on data side - database side, which is in procedure, function or package in database.

 
::Qing
Sign In·View Thread·Permalink
QuestionHow can I describe binary data in XML?susslei cheng28 Sep '02 - 20:29 
Recently, I need to describe binary data in XML(for example:jpeg,bmp data).I know little about smil. Can you send me a source file about this? My email address is clgx_2002@yahoo.com.cn!
Thanks,I hear from your replying!Smile | :)
Sign In·View Thread·Permalink
AnswerRe: How can I describe binary data in XML?memberMS le Roux29 Sep '02 - 20:06 
I have never worked with binary data in XML, so unfortunately I cannot help you with that.
Sign In·View Thread·Permalink
AnswerRe: How can I describe binary data in XML?memberHeath Stewart9 Jan '03 - 11:26 
The common way is with base64 encoding. There are many classes to help you accomplish that in .NET. This is the common approach for MIME and SOAP when dealing with binary data (although you can use a new protocol named DIME that is separate from SOAP but part of the same messages).
 
So, if you had binary data in byte[] buffer, you could get a string value using string base64 = Convert.ToBase64String(buffer) and put that in an element. For a good schema, you should also specify the MIME type, which is "application/octet-stream" using a transfer encoding method of "base64". A MIME header would look like this:
Content-Type: application/octet-stream
Content-Transfer-Encoding: bas64
Content-Disposition: attachment
A typical XML fragment (while the element name is completely arbitrary, it's good practice to remain consistent with the attribute names used) would look like:
<attachment type="application/octet-stream" encoding="base64">SVRTRgA...AAA=</attachment>

 
"Well, I wouldn't say I've been missing it, Bob." - Peter Gibbons
Sign In·View Thread·Permalink
GeneralRendering an xml document with MSIE 5.5memberBalteo5 Sep '02 - 22:13 
Hello,
 
I am trying to render an xml document using an xsl stylesheet and MSIE (client-side transformation).   When I call the document it is displayed as plain xml and not transformed xml.
 
The xml file is as follows:
********************************
<?xml version="1.0"?>
<?xml-stylesheet type="text/xml" href="feuille.xsl"?>
<PERIODIC_TABLE>
   <ATOM STATE="GAS">
      <NAME>Hydrogen</NAME>
      <SYMBOL>H</SYMBOL>
      <ATOMIC_NUMBER>1</ATOMIC_NUMBER>
      <ATOMIC_WEIGHT>1.00794</ATOMIC_WEIGHT>
      <BOILING_POINT UNITS="Kelvin">20.28</BOILING_POINT>
      <MELTING_POINT UNITS="Kelvin">13.81</MELTING_POINT>
      <DENSITY UNITS="grams/cubic centimeter">
         <!-- At 300K, 1 atm -->
         0.0000899
      </DENSITY>
   </ATOM>
   <ATOM STATE="GAS">
      <NAME>Helium</NAME>
      <SYMBOL>He</SYMBOL>
      <ATOMIC_NUMBER>2</ATOMIC_NUMBER>
      <ATOMIC_WEIGHT>4.0026</ATOMIC_WEIGHT>
      <BOILING_POINT UNITS="Kelvin">4.216</BOILING_POINT>
      <MELTING_POINT UNITS="Kelvin">0.95</MELTING_POINT>
      <DENSITY UNITS="grams/cubic centimeter"><!-- At 300K -->
         0.0001785
      </DENSITY>
   </ATOM>
</PERIODIC_TABLE>
********************************
 

The xsl file is as follows:
 
********************************
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:template match="PERIODIC_TABLE">
      <html>
         <xsl:apply-templates/>
      </html>
   </xsl:template>
   <xsl:template match="ATOM">
      <P>
         <xsl:apply-templates/>
      </P>
   </xsl:template>
</xsl:stylesheet>
********************************
 
Can anybody please help?
 
Thanks in advance,
 
Balteo
Sign In·View Thread·Permalink
GeneralRe: Rendering an xml document with MSIE 5.5memberMS le Roux6 Sep '02 - 0:19 
In the xml-stylesheet element in your XML file, try setting your "type" to "text/xsl" instead of "text/xml".
Sign In·View Thread·Permalink
GeneralRe: Rendering an xml document with MSIE 5.5memberBalteo6 Sep '02 - 4:19 
Thank you!
 
Balteo
Sign In·View Thread·Permalink
Last Visit: 31 Dec '99 - 18:00     Last Update: 23 May '13 - 21:24Refresh12 Next »

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

About Article
Using XSLT to group XML elements based on unique ID values in the XML. The XML is transformed into an HTML table.
Type Article
Licence 
First Posted 17 Feb 2002
Views 154,793
Bookmarked 53 times
Top News

The Next Version of Android - Some of What's Coming

Get the Insider News free each morning.
Related Videos
Creating an XSLT Stylesheet
XSLT Elements
Tree List View Built with XSLT and DHTML
sharpcms.net - CMS framework based on XSLT and XML
A (very) simple XSLT test utility
XSLT Programmer's Reference 2nd Edition
XSLT Number to String Transformation II
XSLT Transformations and XSLT Intellisense within the .NET IDE
Generating Word Reports / Documents
Grouping XML elements using XSLT version 1.0
XSLT Unleashed
XSLT transformation with the Processor object
An alternative way to the MSWord Render extension of SQL Reporting Services using XSLT and XML document mapping
ASP.NET 2.0 -- Standard Controls -- XML Control
Project Line Counter Add-In v2.10 for VS.NET and VC6
Creating a Node Tree Fragment in XSLT Using Internet Explorer
XSLT Object Relational Mapping
XSL Transform Code Generator for Visual Studio .NET
XSLT 2.0 programming basics
Applying XSL Transformations
XSLTs to Assembly using XSLTC and ILMerge
A well look HTML menu using XML and XSLT
Permalink | Advertise | Privacy | Mobile
Web02 | 2.6.130523.1 | Last Updated 18 Feb 2002
Article Copyright 2002 by MS le Roux
Everything else Copyright © CodeProject, 1999-2013
Terms of Use
Layout: fixed | fluid