Grouping XML data on attributes, and summing

Posted on 2006-05-25
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
    LVL 3

    Author Comment

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

    <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

    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.

    LVL 3

    Author Comment

    Sorry about that, should have closed this myself.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
    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.
    The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
    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…

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now