Grouping XML using XSLT

Download source files - 2 Kb

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

Member

I live in the Northern Suburbs of Cape Town (South Africa).

Article Top

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. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

My vote of 4

VarunKumarGB

22:23 14 Dec '10

Very nicely written!
Sign In·View Thread·Permalink

My vote of 3

lijo011@yahoo.com

23:57 15 Aug '10

this helped me a good starting for XSLT grouping
Sign In·View Thread·Permalink

Grouping XML data

blindcapt

18:15 31 Mar '09

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

Performance issue

Qing Jiang

3:59 30 Mar '06

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

How can I describe binary data in XML?

lei cheng

20:29 28 Sep '02

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!
Sign In·View Thread·Permalink

Re: How can I describe binary data in XML?

MS le Roux

20:06 29 Sep '02

I have never worked with binary data in XML, so unfortunately I cannot help you with that.
Sign In·View Thread·Permalink

Re: How can I describe binary data in XML?

Heath Stewart

11:26 9 Jan '03

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

Rendering an xml document with MSIE 5.5

Balteo

22:13 5 Sep '02

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">
         
         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">
         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

Re: Rendering an xml document with MSIE 5.5

MS le Roux

0:19 6 Sep '02

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

Re: Rendering an xml document with MSIE 5.5

Balteo

4:19 6 Sep '02

Thank you! Balteo
Sign In·View Thread·Permalink

grouping and xslt

AbsynthE

8:34 22 Aug '02

hola,
i have this xml recordset which looks like this
   <DataSet>
   <User>
      <ID>JoeX</ID>
      <FNAME>Joseph</FNAME>
      <LNAME>Xavier</LNAME>
      <SSN>200-00-0000<SSN
   </User>
   <User>
      <ID>JoeY</ID>
      <FNAME>Jose</FNAME>
      <LNAME>avier</LNAME>
      <SSN>300-00-0000<SSN
   </User>
   <User>
      <ID>JoeG</ID>
      <FNAME>Joli</FNAME>
      <LNAME>Gantz</LNAME>
      <SSN>400-00-0000<SSN
   </User>
   <User>
      <ID>Joe4</ID>
      <FNAME>Joseph</FNAME>
      <LNAME>Gour</LNAME>
      <SSN>500-00-0000<SSN
   </User>
   <User>
      <ID>Joe4</ID>
      <FNAME>Joseph</FNAME>
      <LNAME>Gour</LNAME>
      <SSN>500-00-0000<SSN
   </User>
</DataSet>

Basically I am trying to find a way to transform this xml into something that will have two or however many users in a page element so the output would be something like this.

   <DataSet>
   <page>
      <User>
         <ID>JoeX</ID>
         <FNAME>Joseph</FNAME>
         <LNAME>Xavier</LNAME>
         <SSN>200-00-0000<SSN
      </User>
      <User>
         <ID>JoeY</ID>
         <FNAME>Jose</FNAME>
         <LNAME>avier</LNAME>
         <SSN>300-00-0000<SSN
      </User>
   </page>
   <page>
      <User>
         <ID>JoeG</ID>
         <FNAME>Joli</FNAME>
         <LNAME>Gantz</LNAME>
         <SSN>400-00-0000<SSN
      </User>
      <User>
         <ID>Joe4</ID>
         <FNAME>Joseph</FNAME>
         <LNAME>Gour</LNAME>
         <SSN>500-00-0000<SSN
      </User>
      </page>
   <page>
      <User>
         <ID>Joe4</ID>
         <FNAME>Joseph</FNAME>
         <LNAME>Gour</LNAME>
         <SSN>500-00-0000<SSN
      </User>
      <User>
         <ID>Joe90</ID>
         <FNAME>Joseph90</FNAME>
         <LNAME>Ninety</LNAME>
         <SSN>500-00-0000<SSN
      </User>
   <page>
</DataSet>

any one have any ideas as to how i can achieve this? I can get a page attribute around every user, but i would like to be able to put it around every X number of users, if anyone has any ideas i would really appreciate the help. sorry for the length of this post.

thanks

Why the data are gone?

sshhz

0:48 8 May '02

I ve encounter many many times for those file in .xsl that has this header Just wondering around, each time it has this header, all the xsl codes that i copied from those website as sample returning no values. For this case, there isn't any employees shown. Please advice. sshhz
Sign In·View Thread·Permalink

Re: Why the data are gone?

MarSCoZa

1:06 8 May '02

Mmm... not sure. I have IE6 and MSXML 3. Don't know if the parser version has anything to do with it. I do know that the namespace is case-sensitive. Also that different namespaces support different things. <tip>Use < to show the "<" character and > to show the ">" character.<./tip>
Sign In·View Thread·Permalink

Re: Why the data are gone?

sshhz

15:50 8 May '02

Well, i'm refering to this groupxml article. Currently, my IE is version 5.0, installed MSXML4 in my Professional Windows 2000. Am i under this XML requirement? What is the requirement for having this prolog at my xsl ? http://www.w3.org/1999/XSL/Transform I ve checked at the www.w3.org, i think there isn't any version for this is it? Please help, i'm really confused. sshhz
Sign In·View Thread·Permalink

Re: Why the data are gone?

MS le Roux

20:17 8 May '02

IE5.0's XML support is really buggy. That could have something to do with it. IE5.5 and 6 give better support (and MSXML 3.0 comes with it). MSXML 4 works somewhat differently from version 3, but they work happily side-by-side, so it's OK to have both versions on your PC. I'm just not sure if it makes a difference in which order you install them.

You have to add a namespace to your XSLT file. An XML Namespace declaration indicates that the prefix "xsl" is going to be used for elements defined in the W3C XSLT specification. It doesn't cause your stylesheet to access that site; I think it's just like a long ID indicating what type of XSLT elements you want to use in your stylesheet.

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" is the namespace for ye olde average XSLT.
xmlns:xsl="http://www.w3.org/TR/WD-xsl" is another namespace that has different support. I suggest you search for "XSLT namespace" on Google for more information.

Re: Why the data are gone?

gdltlxb

2:45 5 Jun '02

I think it is the version of IE that cause this problem.When I used IE5.0 (windows98), I met the same problem : all data was gone,then I upgrade to IE6.0 and test groupxml.xml again:everything was ok !! So it is not the problem of codes ,but the version of IE. Tomorrow is always another day!!
Sign In·View Thread·Permalink

Nice going, one thing though...

Paul Watson

21:15 17 Feb '02

Nice article Mars, thanks.

One thing though. People using the unique key method should be aware that MSXML (Microsofts XML parser app) has a bug which kills the performance when using unique keys. They should fix it by the time MSXML 4 Release Version comes out, but till then just be aware of the problem.

regards,
Paul Watson
Bluegrass
Cape Town, South Africa

"The greatest thing you will ever learn is to love, and be loved in return" - Moulin Rouge

Sonork ID: 100.9903 Stormfront

Re: Nice going, one thing though...

MarSCoZa

22:00 17 Feb '02

Now I understand why my XML transformations take ages when processing a long list of Employees. Nice to know that for once it's not my fault. (At least most of the users won't have a problem, because they only see their own stuff.)
Sign In·View Thread·Permalink

Re: Nice going, one thing though...

Matt Berther

7:11 20 Feb '02

Paul Watson wrote: They should fix it by the time MSXML 4 Release Version comes out, but till then just be aware of the problem. Paul, As far as I know, MSXML 4 was RTM'd on 12/14/2001. It is available by clicking here. -- Matt Berther
Sign In·View Thread·Permalink

Re: Nice going, one thing though...

Paul Watson

8:36 20 Feb '02

Matt Berther wrote:
As far as I know, MSXML 4 was RTM'd on 12/14/2001. It is available by clicking here.

Sorry, my mistake.

Basically from what I know, and MS are not that forthcoming with this, there has been a performance bug with xsl:key use from the first time it was implemented in MSXML. The RTM of MSXML 4 did not solve this and neither has the technology preview of MSXML 4.1.

I don't know when they are going to fix the performance issue either. But they are aware of it *throws a party* Smile | :)

regards,
Paul Watson
Bluegrass
Cape Town, South Africa

"The greatest thing you will ever learn is to love, and be loved in return" - Moulin Rouge

Sonork ID: 100.9903 Stormfront