Search code examples
xmlxsltgroupingxslkey

xsl Transformation for Grouping multi layer results

I am hoping that someone can help me asI have been trying to figure out how to make a transformation of a data set into a table structure that is grouped on multiple levels. The first level is the Event Date, the second is the Company Name, and the last is the by the Users.

The following is the XML.

<xmlData>
    <records>
      <record>
        <userid>1</userid>
        <usersname>Jane Doe</usersname>
        <companyname>Company A</companyname>
        <eventdate>01 FEB 2017</eventdate>
        <jeventdate>2457786</jeventdate>
      </record>
      <record>
        <userid>3</userid>
        <usersname>Jane Doe</usersname>
        <companyname>Company B</companyname>
        <eventdate>01 FEB 2017</eventdate>
        <jeventdate>2457786</jeventdate>
      </record>
      <record>
        <userid>2</userid>
        <usersname>Joe Smith</usersname>
        <companyname>Company B</companyname>
        <eventdate>01 DEC 2016</eventdate>
        <jeventdate>2457724</jeventdate>
      </record>
      <record>
        <userid>2</userid>
        <usersname>Joe Smith</usersname>
        <companyname>Company B</companyname>
        <eventdate>01 JAN 2017</eventdate>
        <jeventdate>2457755</jeventdate>
      </record>
      <record>
        <userid>2</userid>
        <usersname>Joe Smith</usersname>
        <companyname>Company B</companyname>
        <eventdate>01 FEB 2017</eventdate>
        <jeventdate>2457786</jeventdate>
      </record>
    </records>
</xmlData>

The results that I am trying to get to is shown in the following simple HTML output.

<h1>01 DEC 2016</h1>
  <h2>Company B</h2>
  <table>
    <tr><td>2</td><td>Joe Smith</td></tr>
  </table>
<h1>01 JAN 2017</h1>
  <h2>Company B</h2>
  <table>
    <tr><td>2</td><td>Joe Smith</td></tr>
  </table>
<h1>01 FEB 2017</h1>
  <h2>Company A</h2>
  <table>
    <tr><td>1</td><td>Jane Doe</td></tr>
  </table>
  <h2>Company B</h2>
  <table>
    <tr><td>3</td><td>Dave Dodd</td></tr>
    <tr><td>2</td><td>Joe Smith</td></tr>
  </table>

The issue I am having is actually two parts. The first is getting the three levels of depth. The second is getting all the records and not just the first few.

Here is the XSL that I have been working with.

<xsl:key name="monthof" match="record" use="eventdate"/>
<xsl:key name="companyof" match="record" use="concat(eventdate,'|',companyname)"/>
<xsl:key name="userof" match="record" use="concat(eventdate,'|',companyname,'|',usersname)"/>

<xsl:template match="xmlData/records">
    <xsl:for-each select="record[key('monthof',eventdate)]">
        <xsl:sort select="jeventdate"/>
        <xsl:variable name="lstEventDate" select="key('monthof',eventdate)" />
        <h2><xsl:value-of select="eventdate"/></h2>
        <xsl:for-each select="key('companyof',concat(eventdate,'|',companyname))">
            <xsl:sort select="companyname"/>
            <h3>
                <xsl:value-of select="companyname"/>
            </h3>
            <table>
                <xsl:for-each select="key('userof',concat(eventdate,'|',companyname,'|',usersname))">
                    <tr>
                        <td><xsl:value-of select="userid"/></td>
                        <td><xsl:value-of select="usersname"/></td>
                    </tr>                                   
                </xsl:for-each>
            </table>
        </xsl:for-each>
    </xsl:for-each>
</xsl:template>

And here is the output that I am getting.

01 DEC 2016

Company B

2 Jane Doe 

01 JAN 2017

Company B

2 Jane Doe 

01 FEB 2017

Company A

1 Joe Smith 

01 FEB 2017

Company B

1 Joe Smith 

Company B

3 Dave Dodd 

01 FEB 2017

Company B

1 Joe Smith 

Company B

2 Jane Doe
Solution

  • The keys you have defined are fine but you need then to use them, as already pointed in a comment, with the Muenchian grouping approach:

    <xsl:template match="xmlData/records">
    <xsl:for-each select="record[generate-id() = generate-id(key('monthof',eventdate)[1])]">
        <xsl:sort select="jeventdate"/>
        <h2><xsl:value-of select="eventdate"/></h2>
        <xsl:for-each select="key('monthof', eventdate)[generate-id() = generate-id(key('companyof',concat(eventdate,'|',companyname))[1])]">
            <xsl:sort select="companyname"/>
            <h3>
                <xsl:value-of select="companyname"/>
            </h3>
            <table>
                <xsl:for-each select="key('companyof',concat(eventdate,'|',companyname))[generate-id() = generate-id(key('userof',concat(eventdate,'|',companyname,'|',usersname))[1])]">
                    <tr>
                        <td><xsl:value-of select="userid"/></td>
                        <td><xsl:value-of select="usersname"/></td>
                    </tr>                                   
                </xsl:for-each>
            </table>
        </xsl:for-each>
    </xsl:for-each>
</xsl:template>

    You might want to check whether you can't move to XSLT 2.0 where it becomes a lot easier doing

    <xsl:template match="xmlData/records">
    <xsl:for-each-group select="record" group-by="eventdate">
        <xsl:sort select="jeventdate"/>
        <h2><xsl:value-of select="eventdate"/></h2>
        <xsl:for-each-group select="current-group()" group-by="companyname">
            <xsl:sort select="companyname"/>
            <h3>
                <xsl:value-of select="companyname"/>
            </h3>
            <table>
                <xsl:for-each-group select="current-group()" group-by="usersname">
                    <tr>
                        <td><xsl:value-of select="userid"/></td>
                        <td><xsl:value-of select="usersname"/></td>
                    </tr>                                   
                </xsl:for-each-group>
            </table>
        </xsl:for-each-group>
    </xsl:for-each-group>
</xsl:template>