XSLT Group by using 2 different elements

Question

I'm new to xslt. I'm trying to group by 2 different elements. First by Worker, then by Pay code. Please see that the amounts sum because of the group by paycode. Below is a before sample xsl, then a sample after xsl that I would like as output.

Before:

<?xml version='1.0' encoding='utf-8'?>
<File   xmlns:is="java:com.workday.esb.intsys.xpath.ParsedIntegrationSystemFunctions"
    xmlns:tv="java:com.workday.esb.intsys.TypedValue">
    <Worker>
        <Detail>
            <EmployeeID>0008765</EmployeeID>
            <FirstName>ROBERT</FirstName>
            <PayCode>RSVEST</PayCode>
            <Amount>5572.800000</Amount>
        </Detail>
        <Detail>
            <EmployeeID>0008765</EmployeeID>
            <FirstName>ROBERT</FirstName>
            <PayCode>FICA</PayCode>
            <Amount>40.000000</Amount>
        </Detail>
    </Worker>
    <Worker>
        <Detail>
            <EmployeeID>0008765</EmployeeID>
            <FirstName>ROBERT</FirstName>
            <PayCode>RSVEST</PayCode>
            <Amount>13545.000000</Amount>
        </Detail>
    </Worker>
    <Worker>
        <Detail>
            <EmployeeID>00012345</EmployeeID>
            <FirstName>RUSSELL</FirstName>
            <PayCode>RSVEST</PayCode>
            <Amount>84811.050000</Amount>
        </Detail>
    </Worker>
</File>

What I would like as output, grouping first by Worker, then group by Pay Code. Amounts sum because of grouping:

<?xml version='1.0' encoding='utf-8'?>
<File xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:tv="java:com.workday.esb.intsys.TypedValue">
    <Worker>
        <Detail>
            <EmployeeID>0008765</EmployeeID>
            <FirstName>ROBERT</FirstName>
            <PayCode>RSVEST</PayCode>
            <Amount>19117.800000</Amount>
        </Detail>
        <Detail>
            <EmployeeID>0008765</EmployeeID>
            <FirstName>ROBERT</FirstName>
            <PayCode>FICA</PayCode>
            <Amount>40.000000</Amount>
         </Detail>
    </Worker>
    <Worker>
        <Detail>
            <EmployeeID>00012345</EmployeeID>
            <FirstName>RUSSELL</FirstName>
            <PayCode>RSVEST</PayCode>
            <Amount>84811.050000</Amount>
        </Detail>
    </Worker>
</File>

Below is my XSL that doesn't work:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" exclude-result-prefixes="xsl wd xsd this env"
    xmlns:wd="urn:com.workday/bsvc" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:this="urn:this-stylesheet">

    <xsl:output indent="yes" method="xml"/>   
    <xsl:template match="/">      
        <File>
            <Worker>
                <xsl:for-each-group select="File" group-by="Worker">
                    <Detail>              
                        <EmployeeID><xsl:value-of select="Worker/current-group()/EmployeeID"></xsl:value-of></EmployeeID>
                        <FirstName><xsl:value-of select="//current-group()//FirstName"></xsl:value-of></FirstName>
                        <PayCode><xsl:value-of select="PayCode"></xsl:value-of></PayCode>
                        <Amount><xsl:value-of select="format-number(sum(current-group()/number(translate(Amount,',',''))),'######.00')"></xsl:value-of></Amount>
                    </Detail>  
                </xsl:for-each-group>
            </Worker>
        </File>
    </xsl:template>
</xsl:stylesheet>

Can someone please put an SL that will transform above to the desired output?
I've been working on this for hours and waving the white flag lol.

Thank you!

Answer 1

If you want to have a group for each EmployeeID, and within each such group a subgroup for each PayCode, then obviously you need to nest two xsl:for-each-group instructions. And the nodes you need to be grouping are the Detail elements, not the root File element of which there only will be one.

XSLT 2.0

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/File">      
    <xsl:copy>
        <xsl:for-each-group select="Worker/Detail" group-by="EmployeeID">
            <Worker>
                <xsl:for-each-group select="current-group()" group-by="PayCode">
                    <Detail>              
                        <xsl:copy-of select="EmployeeID | FirstName | PayCode"/>
                        <Amount>
                            <xsl:value-of select="format-number(sum(current-group()/Amount),'#.000000')"/>
                        </Amount>
                    </Detail>  
                </xsl:for-each-group>
            </Worker>
        </xsl:for-each-group>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>