Search code examples
xmlxsltxslt-1.0xslt-grouping

Sum values in nested groupings in XSLT

I'm trying to do nested grouping in XSLT 1.0 and am facing some issues, below is my XML sample code.

<?xml version="1.0" encoding="iso-8859-1"?>
<INVOICE>
    <GROUP ID="1">
        <GROUP_HEADER_ROW>
            <COL width="2cm" headerAlign="start" headerFormat="text">Product Name</COL>
            <COL width="0cm" headerAlign="start" headerFormat="text">Product Code</COL>
            <COL width="2cm" headerAlign="start" headerFormat="text">Description</COL>
            <COL width="1cm" headerAlign="start" headerFormat="number">QTY</COL>
            <COL width="1cm" headerAlign="end" headerFormat="number">Price</COL>
            <COL width="1cm" headerAlign="end" headerFormat="number">Tax</COL>
            <COL width="1cm" headerAlign="end" headerFormat="number">Amount</COL>
            <COL width="3cm" headerAlign="start" headerFormat="text">Account</COL>
            <COL width="1cm" headerAlign="end" headerFormat="number">VAT</COL>
            <COL width="2cm" headerAlign="start" headerFormat="text">SubscriptionFromDate</COL>
            <COL width="2cm" headerAlign="start" headerFormat="text">SubscriptionToDate</COL>
            <COL width="2cm" headerAlign="start" headerFormat="text">PackageName</COL>
            <COL width="0cm" headerAlign="start" headerFormat="number">PackageID</COL>
            <COL width="0cm" headerAlign="start" headerFormat="text">BundleDesc</COL>
            <COL width="1cm" headerAlign="start" headerFormat="text">Note</COL>
        </GROUP_HEADER_ROW>
        <GROUP_DATA_ROW>
            <COL>Hotline</COL>
            <COL>ProdCode1</COL>
            <COL>Subscription</COL>
            <COL>1</COL>
            <COL>50</COL>
            <COL>0</COL>
            <COL>50</COL>
            <COL>Account1</COL>
            <COL/>
            <COL>2024-09-01T00:00:00.000000-05:00</COL>
            <COL>2024-09-30T23:59:59.000000-05:00</COL>
            <COL/>
            <COL/>
            <COL>GroupValue2</COL>
            <COL/>
        </GROUP_DATA_ROW>
        <GROUP_DATA_ROW>
            <COL>Direct Hit Mobile</COL>
            <COL>ProdCode2</COL>
            <COL>Subscription</COL>
            <COL>1</COL>
            <COL>100</COL>
            <COL>0</COL>
            <COL>100</COL>
            <COL>Account1</COL>
            <COL/>
            <COL>2024-09-01T00:00:00.000000-05:00</COL>
            <COL>2024-09-30T23:59:59.000000-05:00</COL>
            <COL/>
            <COL/>
            <COL>GroupValue2</COL>
            <COL/>
        </GROUP_DATA_ROW>
        <GROUP_DATA_ROW>
            <COL>Direct Hit Mobile</COL>
            <COL>ProdCode3</COL>
            <COL>Subscription</COL>
            <COL>1</COL>
            <COL>50</COL>
            <COL>0</COL>
            <COL>50</COL>
            <COL>Account2</COL>
            <COL/>
            <COL>2024-09-01T00:00:00.000000-05:00</COL>
            <COL>2024-09-30T23:59:59.000000-05:00</COL>
            <COL/>
            <COL/>
            <COL>GroupValue2</COL>
            <COL/>
        </GROUP_DATA_ROW>
        <GROUP_DATA_ROW>
            <COL>Direct Hit</COL>
            <COL>ProdCode4</COL>
            <COL>Subscription</COL>
            <COL>1</COL>
            <COL>100</COL>
            <COL>0</COL>
            <COL>100</COL>
            <COL>Account2</COL>
            <COL/>
            <COL>2024-09-01T00:00:00.000000-05:00</COL>
            <COL>2024-09-30T23:59:59.000000-05:00</COL>
            <COL/>
            <COL/>
            <COL>GroupValue2</COL>
            <COL/>
        </GROUP_DATA_ROW>
    </GROUP>
</INVOICE>

My goal is to create a table with the information group by the below fields:

  • COL[8]
  • COL[14]
  • COL[10]
  • COL[11]

The xslt code I'm using so far is the following..

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:strip-space elements="*"/>
    <xsl:key name="groupAcct" match="/INVOICE/GROUP/GROUP_DATA_ROW" use="COL[8]"/>
    <xsl:key name="groupBunDesc" match="/INVOICE/GROUP/GROUP_DATA_ROW" use="concat(COL[8],COL[14],COL[10],COL[11])"/>

    <xsl:template match="INVOICE">
        <xsl:for-each select="/INVOICE/GROUP/GROUP_DATA_ROW[count(. | key('groupAcct', COL[8])[1]) = 1]">
            <xsl:sort select="COL[8]"/>
            <xsl:variable name="acctName" select="COL[8]"/>
            <h1>
                <xsl:value-of select="$acctName"/>
            </h1>
            <table id="{COL[8]}">
                <th scope="col">
                    <xsl:value-of select="/INVOICE/GROUP/GROUP_HEADER_ROW/COL[2]"/>
                </th>
                <th scope="col">
                    <xsl:value-of select="/INVOICE/GROUP/GROUP_HEADER_ROW/COL[3]"/>
                </th>
                <th scope="col">
                    <xsl:value-of select="/INVOICE/GROUP/GROUP_HEADER_ROW/COL[4]"/>
                </th>
                <th scope="col">
                    <xsl:value-of select="/INVOICE/GROUP/GROUP_HEADER_ROW/COL[5]"/>
                </th>
                <th scope="col">
                    <xsl:value-of select="/INVOICE/GROUP/GROUP_HEADER_ROW/COL[6]"/>
                </th>
                <th scope="col">
                    <xsl:value-of select="/INVOICE/GROUP/GROUP_HEADER_ROW/COL[7]"/>
                </th>
                <xsl:variable name="testVar" select="key('groupAcct', COL[8])"/>
                <xsl:for-each select="$testVar[count(. | key('groupBunDesc', concat(COL[8],COL[14],COL[10],COL[11]))[1]) = 1]">
                    <xsl:if test="COL[14] != ''">
                        <tr>
                            <xsl:value-of select="COL[14]"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="COL[3]"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="COL[4]"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="COL[5]"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="COL[6]"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="COL[7]"/>
                        </tr>
                    </xsl:if>
                </xsl:for-each>
            </table>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

The code that this XSLT is printing is being grouped correctly by account but is not printing only 1 row for each account, below is the current output.

<?xml version="1.0"?>
<h1>Account1</h1>
<table id="Account1">
    <th scope="col">Product Code</th>
    <th scope="col">Description</th>
    <th scope="col">QTY</th>
    <th scope="col">Price</th>
    <th scope="col">Tax</th>
    <th scope="col">Amount</th>
    <tr>GroupValue2</tr>
    <tr>Subscription</tr>
    <tr>1</tr>
    <tr>50</tr>
    <tr>0</tr>
    <tr>50</tr>
</table>
<h1>Account2</h1>
<table id="Account2">
    <th scope="col">Product Code</th>
    <th scope="col">Description</th>
    <th scope="col">QTY</th>
    <th scope="col">Price</th>
    <th scope="col">Tax</th>
    <th scope="col">Amount</th>
    <tr>GroupValue2</tr>
    <tr>Subscription</tr>
    <tr>1</tr>
    <tr>50</tr>
    <tr>0</tr>
    <tr>50</tr>
</table>

The code is working properly but, I am not able to sum the column for Price,Tax and Amount in my code. I am expecting the below output.

<?xml version="1.0"?>
<h1>Account1</h1>
<table id="Account1">
    <th scope="col">Product Code</th>
    <th scope="col">Description</th>
    <th scope="col">QTY</th>
    <th scope="col">Price</th>
    <th scope="col">Tax</th>
    <th scope="col">Amount</th>
    <tr>GroupValue2</tr>
    <tr>Subscription</tr>
    <tr>1</tr>
    <tr>150</tr> <!-- sum the group values-->
    <tr>0</tr>
    <tr>150</tr> <!-- sum the group values-->
</table>
<h1>Account2</h1>
<table id="Account2">
    <th scope="col">Product Code</th>
    <th scope="col">Description</th>
    <th scope="col">QTY</th>
    <th scope="col">Price</th>
    <th scope="col">Tax</th>
    <th scope="col">Amount</th>
    <tr>GroupValue2</tr>
    <tr>Subscription</tr>
    <tr>1</tr>
    <tr>150</tr> <!-- sum the group values-->
    <tr>0</tr>
    <tr>150</tr> <!-- sum the group values-->
</table>

Thanks for your help and knowledge.

Solution

  • Consider below adjustment where you can sum those numeric values against a grouping variable. Also, by using xsl:apply-templates you can reduce repetition.

    <?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
    <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
    <xsl:strip-space elements="*"/>
    
    <xsl:key name="groupAcct" match="GROUP_DATA_ROW" use="COL[8]"/>
    <xsl:key name="groupBunDesc" match="GROUP_DATA_ROW" use="concat(COL[8],COL[14],COL[10],COL[11])"/>

    <xsl:template match="GROUP_HEADER_ROW">
      <xsl:apply-templates select="COL[position() &gt;= 2 and position() &lt;= 7]"/>
    </xsl:template>
    
    <xsl:template match="GROUP_HEADER_ROW/COL">
      <th scope="col">
          <xsl:value-of select="text()"/>
      </th>
    </xsl:template>    
    
    <xsl:template match="INVOICE">
      <xsl:copy>
        <xsl:for-each select="GROUP/GROUP_DATA_ROW[count(. | key('groupAcct', COL[8])[1]) = 1]">
            <xsl:sort select="COL[8]"/>
            <xsl:variable name="curr-group" select="key('groupAcct', COL[8])" />
            <h1>
                <xsl:value-of select="$curr-group/COL[8]"/>
            </h1>
            <table id="{$curr-group/COL[8]}">
                <xsl:apply-templates select="ancestor::GROUP/GROUP_HEADER_ROW"/>
                <xsl:for-each select="$curr-group[count(. | key('groupBunDesc', concat(COL[8],COL[14],COL[10],COL[11]))[1]) = 1]">
                    <xsl:variable name="child-group" select="key('groupBunDesc', concat(COL[8],COL[14],COL[10],COL[11]))" />
                    <xsl:if test="COL[14] != ''">
                        <tr>
                            <xsl:value-of select="$child-group/COL[14]"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="$child-group/COL[3]"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="sum($child-group/COL[4])"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="sum($child-group/COL[5])"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="sum($child-group/COL[6])"/>
                        </tr>
                        <tr>
                            <xsl:value-of select="sum($child-group/COL[7])"/>
                        </tr>
                    </xsl:if>
                </xsl:for-each>
            </table>
        </xsl:for-each>
      </xsl:copy>
    </xsl:template>
    
</xsl:stylesheet>

    Aside: Your desired output is not well-formed since it lacks a root and tags appear to be HTML elements but <tr> tag for table row are not used properly.