Grouping XML data on attributes, and summing

Posted on 2006-05-25
Medium Priority
Last Modified: 2013-11-19
I'm attempting to learn how to do something with XML so I can apply it to a project I'm working on.

Currently, I am able to receive flat xml from a database, which looks something like this:

    <row id="1" name="barilbe" department="IT 6" hrsWorked="6" date="20060101"/>
    <row id="2" name="barilbe" department="Dev Group" hrsWorked="6" date="20060102"/>
    <row id="3" name="barilbe" department="IT 6" hrsWorked="2" date="20061101"/>
    <row id="4" name="barilbe" department="Transition" hrsWorked="6" date="20050101"/>
    <row id="5" name="frank" department="Transition" hrsWorked=1" date="20060201"/>
    <row id="6" name="paul" department="IT 6" hrsWorked="6" date="20050101"/>

What I'd like to do is to take this XML and produce a report in html. Id like to be able to group on the departments (for instance) and get all of the child records to show up, and finally get a subtotal of the number of hours worked at the end of each group.

How would I do this using xslt?

Question by:Krule
  • 2

Author Comment

ID: 16766203
Hmm...I actually solved this problem myself, but I'm willing to award points for anyone that can offer an improved solution (ignore the ugly output, etc)

<?xml version="1.0"?>

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

<xsl:key name="group" match="row" use="@department"/>

<xsl:template match="root">

    <xsl:for-each select="row[generate-id() = generate-id(key('group', @department)[1])]">
            <td colspan="3">
                <xsl:value-of select="@department"/><br/>
        <xsl:for-each select="key('group', @department)">
            <xsl:sort select="@department" />
                    <td> </td>
                    <td><xsl:value-of select="@name"/></td>
                    <td><xsl:value-of select="@hrsWorked"/></td>
            <td>Total Hours for this department:</td>
            <td><xsl:value-of select="sum(key( 'group' , @department )/@hrsWorked)"/></td>
        <td colspan="3">Total hours for all departments: <xsl:value-of select="sum(row/@hrsWorked)"/></td>

LVL 12

Accepted Solution

jkmyoung earned 2000 total points
ID: 16769644
For your first for-each, you could use count instead of generate-id()
<xsl:for-each select="row[count(. | key('group',@department)[1]) = 1]">

You should move the <xsl:sort select="@department" /> back to after the first for-each, instead of the second.


Author Comment

ID: 16931178
Sorry about that, should have closed this myself.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question