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:
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.
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() >= 2 and position() <= 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.