CodeProject: Grouping XML using XSLT. Free source code and programming help

Announcements

	Windows 7 Comp Win a laptop!
	Monthly Competition

Articles

Desktop Development

Web Development

Enterprise Systems

Content Management Server

Microsoft BizTalk Server

SQL Reporting Services

Platforms, Frameworks & Libraries

Windows Communication Foundation

Windows Presentation Foundation

Windows Workflow Foundation

Cryptography & Security

Threads, Processes & IPC

WinHelp / HTMLHelp

Uncategorised Quick Answers

Graphics / Design

Expression

Usability

Development Lifecycle

Debug Tips

Design and Architecture

Uncategorised Technical Blogs

Services

Code-signing Certificates

Job Board

CodeProject VS2008 Addin

Feature Zones

Product Showcase

Code Signing Resources

WhitePapers / Webcasts

ASP.NET Web Hosting

Advanced Search
Add to IE Search

Discuss

Report

14 votes for this article.

Popularity: 4.92 Rating: 4.29 out of 5

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 name="keyTeamID" match="Employee" use="TeamID" />
<xsl:key name="keyEmployeeID" match="Employee" use="EmployeeID" />

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

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

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="//Employee[TeamID=$lngTeamID]" />

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="$lstEmployee[generate-id(.) = generate-id(key('keyEmployeeID', EmployeeID)[1])]">

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

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

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 -->
<xsl:key name="keyTeamID" match="Employee" use="TeamID" />
<xsl:key name="keyEmployeeID" match="Employee" use="EmployeeID" />

<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 -->
    <xsl:for-each select="//Employee[generate-id(.) = generate-id(key('keyTeamID', TeamID)[1])]">
     <xsl:variable name="lngTeamID"><xsl:value-of select="TeamID" /></xsl:variable>
     <!-- Select all the Employees belonging to the Team -->
     <xsl:variable name="lstEmployee" select="//Employee[TeamID=$lngTeamID]" />
     <!-- Show details for Employees in Team -->
     <xsl:call-template name="ShowEmployeesInTeam">
      <xsl:with-param name="lstEmployee" select="$lstEmployee" />
     </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">
 <xsl:param name="lstEmployee" />
 
 <!-- Show the name of the Team currently being processed -->
 <tr>
  <td colspan="4" class="DarkBack">TEAM:&#160;<xsl:value-of select="$lstEmployee[1]/TeamName" /></td>
  <td colspan="1" class="DarkBack RightJustified">HOURS</td>
 </tr>
 
 <!-- Show the total hours for each Employee in the Team -->
 <xsl:for-each select="$lstEmployee[generate-id(.) = generate-id(key('keyEmployeeID', EmployeeID)[1])]">
  <xsl:variable name="lngEmployeeID" select="EmployeeID" />
  <!-- Show details of each Employee -->
  <tr>
   <td colspan="4">
    <xsl:value-of select="$lstEmployee[EmployeeID=$lngEmployeeID]/Name" />
    &#160;<xsl:value-of select="$lstEmployee[EmployeeID=$lngEmployeeID]/Surname" />
   </td>
   <td colspan="1" class="RightJustified">
    <!-- Show the total hours for the current Employee -->
    <xsl:value-of select="sum($lstEmployee[EmployeeID=$lngEmployeeID]/Hours)" />
   </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

Member

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

Occupation:	Web Developer
Location:	South Africa

Other popular XML articles:

C# and XML Source Code Documentation
Demonstrates how to use C#.NET's XML source documentation to produce professional, indexed and searchable source documentation.
Sending Files in Chunks with MTOM Web Services and .NET 2.0
How to send large files across web services in small chunks using MTOM (WSE 3)
Microsoft Office XML formats ? Defective by design
Case studies why Microsoft Office XML formats are broken
Load and save objects to XML using serialization
A C# program to demonstrate loading and saving an object to an XML file using XML serialization encapsulated in a wrapper class.
XmlSerializer and 'not expected' Inherited Types
A solution to XmlSerializing inherited types not known until runtime.

Article Top

You must Sign In to use this message board.

FAQ
Noise Tolerance Layout Per page

Msgs 1 to 18 of 18 (Total in Forum: 18) (Refresh)

FirstPrevNext

Grouping XML data

blindcapt

19: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

4: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

21: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

21: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

12: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

23: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

1: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

5:19 6 Sep '02

Thank you! Balteo
Sign In·View Thread·PermaLink

grouping and xslt

AbsynthE

9: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

2.00/5 (1 vote)

Why the data are gone?

sshhz

1: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

2: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

16: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

21: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

3: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

22: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

23: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

8: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

9: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

Last Visit: 2:51 9 Nov '09 Last Update: 2:51 9 Nov '09

General News Question Answer Joke Rant Admin

PermaLink | Privacy | Terms of Use
Last Updated: 17 Feb 2002
Editor: Paul Watson