使用XSLT的group by功能按SSN和贡献类型汇总金额

需求 #

按SSN分组，当ContributionType（对应wd:ContributionType/@wd:Descriptor）相同时，对wd:Amount进行求和，预期输出记录为：
D|123-45-6789|20230315|2023|EE|103.76|ABC|DEF|FSA

输入XML #

<wd:Report_Data xmlns:wd="urn:com.workday.report/Test_Deductions">
    <wd:Report_Entry>
        <wd:Worker>
            <wd:SSN>123-45-6789</wd:SSN>
            <wd:First_Name>ABC</wd:First_Name>
            <wd:Last_Name>DEF</wd:Last_Name>
            <wd:Deduction_Effective_Date>20230101</wd:Deduction_Effective_Date>
            <wd:CF_HE_FSA_Plan_Year_Date>2023-01-01-08:00</wd:CF_HE_FSA_Plan_Year_Date>
        </wd:Worker>
        <wd:DepositDate>2023-03-15-07:00</wd:DepositDate>
        <wd:Result_Lines>
            <wd:Amount>51.88</wd:Amount>
            <wd:ContributionType wd:Descriptor="FSA">
                <wd:ID wd:type="WID">ed9b8c781ae64a03b14e36702640391e</wd:ID>
                <wd:ID wd:type="Deduction_Code">FSAH</wd:ID>
            </wd:ContributionType>
        </wd:Result_Lines>
        <wd:Result_Lines>
            <wd:Amount>51.88</wd:Amount>
            <wd:ContributionType wd:Descriptor="FSA">
                <wd:ID wd:type="WID">ed9b8c781ae64a03b14e36702640391e</wd:ID>
                <wd:ID wd:type="Deduction_Code">FSAH</wd:ID>
            </wd:ContributionType>
        </wd:Result_Lines>
    </wd:Report_Entry>
</wd:Report_Data>

原尝试的XSLT（未实现求和） #

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions"
    xmlns:wd="urn:com.workday.report/Test_Deductions"
    exclude-result-prefixes="xsl" version="2.0">

    <xsl:output method="text"/>
    <!--  Define linefeed & delimiter variables  -->
    <xsl:variable name="linefeed" select="'&#xD;&#xA;'"/>
    <xsl:variable name="delimiter" select="'|'"/>
    <xsl:template match="/wd:Report_Data/wd:Report_Entry">
        <xsl:for-each select="wd:Result_Lines">
        <xsl:value-of select="'D'"/>
        <xsl:value-of select="$delimiter"/>
        <xsl:value-of select="../wd:Worker/wd:SSN"/>
        <xsl:value-of select="$delimiter"/>    
            <xsl:value-of select="substring(replace(../wd:DepositDate,'-',''),1,8)"/>
        <xsl:value-of select="$delimiter"/>
        <xsl:value-of select="substring(../wd:Worker/wd:CF_HE_FSA_Plan_Year_Date,1,4)"/>
        <xsl:value-of select="$delimiter"/>
        <xsl:value-of select="'EE'"/>
        <xsl:value-of select="$delimiter"/>
            <xsl:value-of select="format-number(wd:Amount,'#.00')"/>
        <xsl:value-of select="$delimiter"/>
        <xsl:value-of select="../wd:Worker/wd:First_Name"/>
        <xsl:value-of select="$delimiter"/>
        <xsl:value-of select="../wd:Worker/wd:Last_Name"/>
        <xsl:value-of select="$delimiter"/>
        <xsl:choose>
            <xsl:when test="wd:ContributionType/@wd:Descriptor= 'FSA'">
                <xsl:value-of select="'FSA'"/>
            </xsl:when>
            <xsl:when test="wd:ContributionType/@wd:Descriptor = 'Dependent Care FSA'">
                <xsl:value-of select="'DCFSA'"/>
            </xsl:when>
            <xsl:when test="wd:ContributionType/@wd:Descriptor = 'Limited Health Care FSA'">
                <xsl:value-of select="'LPFSA'"/>
            </xsl:when>
        </xsl:choose>
        <xsl:value-of select="$linefeed"/>
        </xsl:for-each>
     </xsl:template>
</xsl:stylesheet>

修改后的XSLT（实现分组求和） #

利用XSLT 2.0的<xsl:for-each-group>特性，按SSN和贡献类型的Descriptor进行分组，对组内的wd:Amount求和：

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions"
    xmlns:wd="urn:com.workday.report/Test_Deductions"
    exclude-result-prefixes="xsl" version="2.0">

    <xsl:output method="text"/>
    <!--  Define linefeed & delimiter variables  -->
    <xsl:variable name="linefeed" select="'&#xD;&#xA;'"/>
    <xsl:variable name="delimiter" select="'|'"/>

    <xsl:template match="/wd:Report_Data/wd:Report_Entry">
        <!-- 按SSN和贡献类型的Descriptor分组 -->
        <xsl:for-each-group select="wd:Result_Lines" group-by="concat(../wd:Worker/wd:SSN, '|', wd:ContributionType/@wd:Descriptor)">
            <xsl:value-of select="'D'"/>
            <xsl:value-of select="$delimiter"/>
            <!-- 取组内第一条的SSN -->
            <xsl:value-of select="../wd:Worker/wd:SSN"/>
            <xsl:value-of select="$delimiter"/>    
            <xsl:value-of select="substring(replace(../wd:DepositDate,'-',''),1,8)"/>
            <xsl:value-of select="$delimiter"/>
            <xsl:value-of select="substring(../wd:Worker/wd:CF_HE_FSA_Plan_Year_Date,1,4)"/>
            <xsl:value-of select="$delimiter"/>
            <xsl:value-of select="'EE'"/>
            <xsl:value-of select="$delimiter"/>
            <!-- 对组内Amount求和并格式化 -->
            <xsl:value-of select="format-number(sum(current-group()/wd:Amount), '#.00')"/>
            <xsl:value-of select="$delimiter"/>
            <xsl:value-of select="../wd:Worker/wd:First_Name"/>
            <xsl:value-of select="$delimiter"/>
            <xsl:value-of select="../wd:Worker/wd:Last_Name"/>
            <xsl:value-of select="$delimiter"/>
            <!-- 取组内第一条的贡献类型映射值 -->
            <xsl:choose>
                <xsl:when test="current-group()[1]/wd:ContributionType/@wd:Descriptor= 'FSA'">
                    <xsl:value-of select="'FSA'"/>
                </xsl:when>
                <xsl:when test="current-group()[1]/wd:ContributionType/@wd:Descriptor = 'Dependent Care FSA'">
                    <xsl:value-of select="'DCFSA'"/>
                </xsl:when>
                <xsl:when test="current-group()[1]/wd:ContributionType/@wd:Descriptor = 'Limited Health Care FSA'">
                    <xsl:value-of select="'LPFSA'"/>
                </xsl:when>
            </xsl:choose>
            <xsl:value-of select="$linefeed"/>
        </xsl:for-each-group>
     </xsl:template>
</xsl:stylesheet>

输出结果 #

D|123-45-6789|20230315|2023|EE|103.76|ABC|DEF|FSA

内容的提问来源于stack exchange，提问作者Buddi