XSL to group by and subtotal on key

I want to group and subtotal an xml based on two fields (Byordnum and Num) so I’ve created a key for them but I don’t get the right result / get the subtotals.

Input xml:

<DeliHomeBasic_DesAdv>
    <InboundDlvNumber/>
    <ExternalDlvNumber>WSP0059837_SCU0000001_1</ExternalDlvNumber>
    <DeliveryDate>2023-01-16</DeliveryDate>
    <SupplierName>Weekamp Doors SRO</SupplierName>
    <Cpss>
        <Cps>
            <Line>1</Line>
            <Subline>0</Subline>
            <Pacs>
                <Pac>
                    <Qua>1</Qua>
                </Pac>
            </Pacs>
        </Cps>
        <Cps>
            <Line>2</Line>
            <Subline>1</Subline>
            <Pacs>
                <Pac>
                    <Qua>1</Qua>
                    <Iso>2</Iso>
                    <Sscc>WPA0135955</Sscc>
                    <Sscc2>80689</Sscc2>
                </Pac>
            </Pacs>
            <Lines>
                <Line>
                    <Num>00960</Num>
                    <Byart>WK_RA_UITZET</Byart>
                    <Desc>Top hang window, 895x1370x54 FSCHW</Desc>
                    <Delqua>1</Delqua>
                    <Byordnum>4500428486</Byordnum>
                </Line>
                <Line>
                    <Num>00960</Num>
                    <Byart>WK_RA_UITZET</Byart>
                    <Desc>Top hang window, 895x1370x54 FSCHW</Desc>
                    <Delqua>1</Delqua>
                    <Byordnum>4500428486</Byordnum>
                </Line>
                <Line>
                    <Num>00960</Num>
                    <Byart>WK_RA_UITZET</Byart>
                    <Desc>Top hang window, 895x1370x54 FSCHW</Desc>
                    <Delqua>1</Delqua>
                    <Byordnum>4500428486</Byordnum>
                </Line>
                <Line>
                    <Num>00970</Num>
                    <Byart>WK_RA_UITZET</Byart>
                    <Desc>Top hang window, 895x1495x54 FSCHW</Desc>
                    <Delqua>1</Delqua>
                    <Byordnum>4500428486</Byordnum>
                </Line>
                <Line>
                    <Num>00970</Num>
                    <Byart>WK_RA_UITZET</Byart>
                    <Desc>Top hang window, 895x1495x54 FSCHW</Desc>
                    <Delqua>1</Delqua>
                    <Byordnum>4500428486</Byordnum>
                </Line>
                <Line>
                    <Num>00990</Num>
                    <Gtin/>
                    <Byart>WK_TB_DEUR</Byart>
                    <Desc>WK046, 830x2120x38 FSCĀ® cert. Premium Hardwood</Desc>
                    <Delqua>1</Delqua>
                    <Byordnum>4500428486</Byordnum>
                    <Suordnum>SOR0061481</Suordnum>
                </Line>
            </Lines>
        </Cps>
    </Cpss>
</DeliHomeBasic_DesAdv>

XSL:

  <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:strip-space elements="*"/>
    <xsl:output indent="yes"/>
    
      <xsl:key name="k1" match="/Line" use="concat(Byordnum, '|', Num, '|', generate-id(..))"/>
    
      <!-- identity transform -->
      <xsl:template match="@*|node()">
        <xsl:copy>
          <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
      </xsl:template>
    
     <xsl:template match="/Lines">
        <xsl:copy>
          <xsl:for-each select="/Lines[generate-id()=generate-id(key('k1', concat(Byordnum,'|',Num, '|', generate-id(..)))[1])]">
            <xsl:copy>
              <xsl:copy-of select="Num"/>
              <xsl:copy-of select="Gtin"/>
              <xsl:copy-of select="Byart"/>
              <Delqua>
                <xsl:value-of select="sum(key('k1', concat(Byordnum,'|',Num, '|', generate-id(..)))/Delqua)" />
              </Delqua>
              <xsl:copy-of select="Byordnum"/>
              <xsl:copy-of select="Suordnum"/>
            </xsl:copy>
          </xsl:for-each>
        </xsl:copy>
      </xsl:template>
    </xsl:stylesheet>

Current result:
Same as input

Required result:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<DeliHomeBasic_DesAdv>
    <InboundDlvNumber/>
    <ExternalDlvNumber>WSP0059837_SCU0000001_1</ExternalDlvNumber>
    <DeliveryDate>2023-01-16</DeliveryDate>
    <SupplierName>Weekamp Doors SRO</SupplierName>
    <Cpss>
        <Cps>
            <Line>1</Line>
            <Subline>0</Subline>
            <Pacs>
                <Pac>
                    <Qua>1</Qua>
                </Pac>
            </Pacs>
        </Cps>
        <Cps>
            <Line>2</Line>
            <Subline>1</Subline>
            <Pacs>
                <Pac>
                    <Qua>1</Qua>
                    <Iso>2</Iso>
                    <Sscc>WPA0135955</Sscc>
                    <Sscc2>80689</Sscc2>
                </Pac>
            </Pacs>
            <Lines>
                <Line>
                    <Num>00960</Num>
                    <Byart>WK_RA_UITZET</Byart>
                    <Desc>Top hang window, 895x1370x54 FSCHW</Desc>
                    <Delqua>3</Delqua>
                    <Byordnum>4500428486</Byordnum>
                </Line>
                <Line>
                    <Num>00970</Num>
                    <Byart>WK_RA_UITZET</Byart>
                    <Desc>Top hang window, 895x1495x54 FSCHW</Desc>
                    <Delqua>2</Delqua>
                    <Byordnum>4500428486</Byordnum>
                </Line>
                <Line>
                    <Num>00990</Num>
                    <Gtin/>
                    <Byart>WK_TB_DEUR</Byart>
                    <Desc>WK046, 830x2120x38 FSCĀ® cert. Premium Hardwood</Desc>
                    <Delqua>1</Delqua>
                    <Byordnum>4500428486</Byordnum>
                    <Suordnum>SOR0061481</Suordnum>
                </Line>
            </Lines>
        </Cps>
    </Cpss>
</DeliHomeBasic_DesAdv>

Hope I didn’t make any typo’s in copying the sample xml / code..

https://xsltfiddle.liberty-development.net/6qLYEoS/2

Kind regards,

Mike

>Solution :

Most match patterns you used were off:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:strip-space elements="*"/>
    <xsl:output indent="yes"/>
    
      <xsl:key name="k1" match="Lines/Line" use="concat(Byordnum, '|', Num, '|', generate-id(..))"/>
    
      <!-- identity transform -->
      <xsl:template match="@*|node()">
        <xsl:copy>
          <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
      </xsl:template>
    
     <xsl:template match="Lines">
        <xsl:copy>
          <xsl:for-each select="Line[generate-id()=generate-id(key('k1', concat(Byordnum,'|',Num, '|', generate-id(..)))[1])]">
            <xsl:copy>
              <xsl:copy-of select="Num"/>
              <xsl:copy-of select="Gtin"/>
              <xsl:copy-of select="Byart"/>
              <Delqua>
                <xsl:value-of select="sum(key('k1', concat(Byordnum,'|',Num, '|', generate-id(..)))/Delqua)" />
              </Delqua>
              <xsl:copy-of select="Byordnum"/>
              <xsl:copy-of select="Suordnum"/>
            </xsl:copy>
          </xsl:for-each>
        </xsl:copy>
      </xsl:template>
    </xsl:stylesheet>

Leave a Reply