How to group parent attribute by using Muenchian Method?

Question

I'm the beginner of XSLT and feel confused about Muenchian grouping method. Here is my XML document

<?xml   version='1.0'?> 
<?xml-stylesheet type="text/xsl"    href="test.xslt"?>
<catalog>
    <cd PurchaseDate="20000101">
        <title>Empire Burlesque</title>
        <artist>Bob Dylan</artist>
        <country>USA</country>
        <quantity>20</quantity>
        <price>10.90</price>
    </cd>
    <cd PurchaseDate="20000101">
        <title>Hide your heart</title>
        <artist>Bonnie Tyler</artist>
        <country>UK</country>
        <quantity>10</quantity>
        <price>9.90</price>
    </cd>
    <cd PurchaseDate="20000102">
        <title>Greatest Hits</title>
        <artist>Dolly Parton</artist>
        <country>USA</country>
        <quantity>15</quantity>
        <price>9.90</price>
    </cd>
    <cd PurchaseDate="20000101">
        <title>Still got the blues</title>
        <artist>Gary Moore</artist>
        <country>UK</country>
        <quantity>5</quantity>
        <price>10.20</price>
    </cd>
    <cd PurchaseDate="20000103">
        <title>Eros</title>
        <artist>Eros Ramazzotti</artist>
        <country>EU</country>
        <quantity>6</quantity>
        <price>9.90</price>
    </cd>
    <cd PurchaseDate="20000103">
        <title>One night only</title>
        <artist>Bee Gees</artist>
        <country>UK</country>
        <quantity>16</quantity>
        <price>10.90</price>
    </cd>
    <cd PurchaseDate="20000102">
        <title>Sylvias Mother</title>
        <artist>Dr.Hook</artist>
        <country>UK</country>
        <quantity>3</quantity>
        <price>8.10</price>
    </cd>
    <cd PurchaseDate="20000101">
        <title>Maggie May</title>
        <artist>Rod Stewart</artist>
        <country>UK</country>
        <quantity>8</quantity>
        <price>8.50</price>
    </cd>
    <cd PurchaseDate="20000103">
        <title>Romanza</title>
        <artist>Andrea Bocelli</artist>
        <country>EU</country>
        <quantity>30</quantity>
        <price>10.80</price>
    </cd>
</catalog>

And XSLT

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" version="1.0" indent="yes" />
    <xsl:key name="kByCountry" match="cd" use="country" />
    <xsl:output method="html" />
    <xsl:template match="catalog">
        <html>
            <body>
                <table border="1">
                    <xsl:for-each select="cd[count(.|key('kByCountry',country)[1]) = 1]">
                        <xsl:sort select="country" />
                        <tr bgcolor="#9acd32">
                            <td colspan="4">Country:<xsl:value-of select="country" /></td>
                        </tr>
                        <tr>
                            <td>Purchase Date</td>
                            <td>Quantity</td>
                            <td>Unit Price</td>
                            <td>Total</td>
                        </tr>
                        <tr>
                            <td>?date?</td>
                            <td><xsl:value-of select="quantity" />  </td>
                            <td><xsl:value-of select="price" /></td>
                            <td><xsl:value-of select="price*quantity" /></td>
                        </tr>
                        <tr>
                            <td colspan="3" align="right">Sub-total</td>
                            <td>?how to count subtotal together?</td>
                        </tr>
                    </xsl:for-each>
                    <tr>
                        <td colspan="3" align="right">Grand-total</td>
                        <td>?how to count all subtotal together?</td>
                    </tr>
                </table>
            </body>
        </html>
    </xsl:template>
</xsl:stylesheet>

My question is how to list all the Purchase Date in the country group. So that I can count the total amount following the country

score 1 · Answer 1 · edited May 23 '17 at 12:18

This transformation:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:ext="http://exslt.org/common" exclude-result-prefixes="ext">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:key name="kCDPurchByCountryDate" match="cd"
  use="concat(@PurchaseDate,'+', country)"/>

 <xsl:template match="/">
  <xsl:variable name="vrtfPass1">
   <xsl:apply-templates/>
  </xsl:variable>

  <xsl:apply-templates select="ext:node-set($vrtfPass1)/*">
   <xsl:sort select="@country"/>
   <xsl:sort select="@PurchaseDate" order="descending"/>
  </xsl:apply-templates>
 </xsl:template>

 <xsl:template match=
 "cd[generate-id()
    =generate-id(key('kCDPurchByCountryDate',
                     concat(@PurchaseDate,'+', country)
                     )[1]
                 )]">
  <trans country="{country}" PurchaseDate="{@PurchaseDate}">
   <amount><xsl:value-of select="quantity*price"/></amount>
   <xsl:apply-templates mode="group" select=
    "key('kCDPurchByCountryDate',concat(@PurchaseDate,'+', country))
        [position() > 1]
    "/>
  </trans>
 </xsl:template>

 <xsl:template match="cd" mode="group">
   <amount><xsl:value-of select="quantity*price"/></amount>
 </xsl:template>
 <xsl:template match="text()"/>

 <xsl:template match="trans">
  <xsl:copy>
   <xsl:copy-of select="@*"/>
   <total><xsl:value-of select="sum(amount)"/></total>
  </xsl:copy>
 </xsl:template>
</xsl:stylesheet>

when applied on the provided XML document:

<catalog>
    <cd PurchaseDate="20000101">
        <title>Empire Burlesque</title>
        <artist>Bob Dylan</artist>
        <country>USA</country>
        <quantity>20</quantity>
        <price>10.90</price>
    </cd>
    <cd PurchaseDate="20000101">
        <title>Hide your heart</title>
        <artist>Bonnie Tyler</artist>
        <country>UK</country>
        <quantity>10</quantity>
        <price>9.90</price>
    </cd>
    <cd PurchaseDate="20000102">
        <title>Greatest Hits</title>
        <artist>Dolly Parton</artist>
        <country>USA</country>
        <quantity>15</quantity>
        <price>9.90</price>
    </cd>
    <cd PurchaseDate="20000101">
        <title>Still got the blues</title>
        <artist>Gary Moore</artist>
        <country>UK</country>
        <quantity>5</quantity>
        <price>10.20</price>
    </cd>
    <cd PurchaseDate="20000103">
        <title>Eros</title>
        <artist>Eros Ramazzotti</artist>
        <country>EU</country>
        <quantity>6</quantity>
        <price>9.90</price>
    </cd>
    <cd PurchaseDate="20000103">
        <title>One night only</title>
        <artist>Bee Gees</artist>
        <country>UK</country>
        <quantity>16</quantity>
        <price>10.90</price>
    </cd>
    <cd PurchaseDate="20000102">
        <title>Sylvias Mother</title>
        <artist>Dr.Hook</artist>
        <country>UK</country>
        <quantity>3</quantity>
        <price>8.10</price>
    </cd>
    <cd PurchaseDate="20000101">
        <title>Maggie May</title>
        <artist>Rod Stewart</artist>
        <country>UK</country>
        <quantity>8</quantity>
        <price>8.50</price>
    </cd>
    <cd PurchaseDate="20000103">
        <title>Romanza</title>
        <artist>Andrea Bocelli</artist>
        <country>EU</country>
        <quantity>30</quantity>
        <price>10.80</price>
    </cd>
</catalog>

produces the wanted, correct result:

<trans country="EU" PurchaseDate="20000103">
   <total>383.4</total>
</trans>
<trans country="UK" PurchaseDate="20000103">
   <total>174.4</total>
</trans>
<trans country="UK" PurchaseDate="20000102">
   <total>24.299999999999997</total>
</trans>
<trans country="UK" PurchaseDate="20000101">
   <total>218</total>
</trans>
<trans country="USA" PurchaseDate="20000102">
   <total>148.5</total>
</trans>
<trans country="USA" PurchaseDate="20000101">
   <total>218</total>
</trans>

Explanation:

This is a non-recursive, two-pass transformation. For a recursive XSLT 1.0 solution of the ptoblem of multiplying numbers and then summing the results of the multiplications, see the answer to this question: Multiply 2 numbers and then sum with XSLT:
The first pass groups by country and purchase date, using Muenchian grouping method with composite key.
For each group multiple amount elements are produced.
The second pass shallow-copies the transaction elements created in the first pass. It replaces the amount children with a single total element.

II. XSLT 2.0 solution:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>

 <xsl:template match="/*">
  <xsl:for-each-group select="cd" group-by="concat(country,'+',@PurchaseDate)">
    <xsl:sort select="country"/>
    <xsl:sort select="@PurchaseDate" order="descending"/>

    <trans country="{country}" PurchaseDate="{@PurchaseDate}">
      <total><xsl:sequence select="sum(current-group()/(price*quantity))"/></total>
    </trans>
  </xsl:for-each-group>
 </xsl:template>
</xsl:stylesheet>

score 0 · Accepted Answer · edited May 23 '17 at 11:43

Although you haven't shown what you expect to see, here's a solution that, I think, gives you what you want. Note that no <xsl:for-each> is needed; this <xsl:template>-based solution is a bit more flexible.

When this XSLT:

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

  <xsl:key name="kCdByCountry" match="cd" use="country"/>

  <xsl:template match="/*">
    <html>
      <body>
        <table border="1">
          <xsl:apply-templates
             select="cd[generate-id() =                        
                        generate-id(key('kCdByCountry', country)[1])]">
            <xsl:sort select="country"/>
          </xsl:apply-templates>
          <tr bgcolor="#9acd32">
            <td colspan="4">
              <xsl:text>Total: </xsl:text>
              <xsl:call-template name="sumProducts">
                <xsl:with-param name="pElemList" select="/*/*"/>
              </xsl:call-template>
            </td>
          </tr>
        </table>
      </body>
    </html>
  </xsl:template>

  <xsl:template match="cd">
    <tr bgcolor="#9acd32">
      <td colspan="4">
        <xsl:text>Country: </xsl:text>
        <xsl:value-of select="country"/>
      </td>
    </tr>
    <tr>
      <td>Purchase Date</td>
      <td>Quantity</td>
      <td>Unit Price</td>
      <td>Total</td>
    </tr>
    <xsl:apply-templates select="key('kCdByCountry', country)" mode="values">
      <xsl:sort select="@PurchaseDate" data-type="number"/>
    </xsl:apply-templates>
    <tr bgcolor="#9acd32">
      <td colspan="4">
        <xsl:text>Subtotal: </xsl:text>
        <xsl:call-template name="sumProducts">
          <xsl:with-param
            name="pElemList"
            select="key('kCdByCountry', country)"/>
        </xsl:call-template>
      </td>
    </tr>
  </xsl:template>

  <xsl:template match="cd" mode="values">
    <tr>
      <td>
        <xsl:value-of select="@PurchaseDate"/>
      </td>
      <td>
        <xsl:value-of select="quantity"/>
      </td>
      <td>
        <xsl:value-of select="price"/>
      </td>
      <td>
        <xsl:value-of select="quantity * price"/>
      </td>
    </tr>
  </xsl:template>

  <xsl:template name="sumProducts">
    <xsl:param name="pElemList"/>
    <xsl:param name="pTotal" select="0"/>
    <xsl:choose>
      <xsl:when test="$pElemList">
        <xsl:variable name="vCurrentElem" select="$pElemList[1]"/>
        <xsl:call-template name="sumProducts">
          <xsl:with-param
            name="pElemList"
            select="$pElemList[position() &gt; 1]"/>
          <xsl:with-param
            name="pTotal"
            select="$pTotal + $vCurrentElem/price * $vCurrentElem/quantity"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$pTotal"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

...is applied against the provided XML:

<?xml version="1.0" encoding="UTF-8"?>
<catalog>
  <cd PurchaseDate="20000101">
    <title>Empire Burlesque</title>
    <artist>Bob Dylan</artist>
    <country>USA</country>
    <quantity>20</quantity>
    <price>10.90</price>
  </cd>
  <cd PurchaseDate="20000101">
    <title>Hide your heart</title>
    <artist>Bonnie Tyler</artist>
    <country>UK</country>
    <quantity>10</quantity>
    <price>9.90</price>
  </cd>
  <cd PurchaseDate="20000102">
    <title>Greatest Hits</title>
    <artist>Dolly Parton</artist>
    <country>USA</country>
    <quantity>15</quantity>
    <price>9.90</price>
  </cd>
  <cd PurchaseDate="20000101">
    <title>Still got the blues</title>
    <artist>Gary Moore</artist>
    <country>UK</country>
    <quantity>5</quantity>
    <price>10.20</price>
  </cd>
  <cd PurchaseDate="20000103">
    <title>Eros</title>
    <artist>Eros Ramazzotti</artist>
    <country>EU</country>
    <quantity>6</quantity>
    <price>9.90</price>
  </cd>
  <cd PurchaseDate="20000103">
    <title>One night only</title>
    <artist>Bee Gees</artist>
    <country>UK</country>
    <quantity>16</quantity>
    <price>10.90</price>
  </cd>
  <cd PurchaseDate="20000102">
    <title>Sylvias Mother</title>
    <artist>Dr.Hook</artist>
    <country>UK</country>
    <quantity>3</quantity>
    <price>8.10</price>
  </cd>
  <cd PurchaseDate="20000101">
    <title>Maggie May</title>
    <artist>Rod Stewart</artist>
    <country>UK</country>
    <quantity>8</quantity>
    <price>8.50</price>
  </cd>
  <cd PurchaseDate="20000103">
    <title>Romanza</title>
    <artist>Andrea Bocelli</artist>
    <country>EU</country>
    <quantity>30</quantity>
    <price>10.80</price>
  </cd>
</catalog>

..the (wanted?) result is produced:

<html>
  <body>
    <table border="1">
      <tr bgcolor="#9acd32">
        <td colspan="4">Country: EU</td>
      </tr>
      <tr>
        <td>Purchase Date</td>
        <td>Quantity</td>
        <td>Unit Price</td>
        <td>Total</td>
      </tr>
      <tr>
        <td>20000103</td>
        <td>6</td>
        <td>9.90</td>
        <td>59.4</td>
      </tr>
      <tr>
        <td>20000103</td>
        <td>30</td>
        <td>10.80</td>
        <td>324</td>
      </tr>
      <tr bgcolor="#9acd32">
        <td colspan="4">Subtotal: 383.4</td>
      </tr>
      <tr bgcolor="#9acd32">
        <td colspan="4">Country: UK</td>
      </tr>
      <tr>
        <td>Purchase Date</td>
        <td>Quantity</td>
        <td>Unit Price</td>
        <td>Total</td>
      </tr>
      <tr>
        <td>20000101</td>
        <td>10</td>
        <td>9.90</td>
        <td>99</td>
      </tr>
      <tr>
        <td>20000101</td>
        <td>5</td>
        <td>10.20</td>
        <td>51</td>
      </tr>
      <tr>
        <td>20000101</td>
        <td>8</td>
        <td>8.50</td>
        <td>68</td>
      </tr>
      <tr>
        <td>20000102</td>
        <td>3</td>
        <td>8.10</td>
        <td>24.3</td>
      </tr>
      <tr>
        <td>20000103</td>
        <td>16</td>
        <td>10.90</td>
        <td>174.4</td>
      </tr>
      <tr bgcolor="#9acd32">
        <td colspan="4">Subtotal: 416.7</td>
      </tr>
      <tr bgcolor="#9acd32">
        <td colspan="4">Country: USA</td>
      </tr>
      <tr>
        <td>Purchase Date</td>
        <td>Quantity</td>
        <td>Unit Price</td>
        <td>Total</td>
      </tr>
      <tr>
        <td>20000101</td>
        <td>20</td>
        <td>10.90</td>
        <td>218</td>
      </tr>
      <tr>
        <td>20000102</td>
        <td>15</td>
        <td>9.90</td>
        <td>148.5</td>
      </tr>
      <tr bgcolor="#9acd32">
        <td colspan="4">Subtotal: 366.5</td>
      </tr>
      <tr bgcolor="#9acd32">
        <td colspan="4">Total: 1166.6</td>
      </tr>
    </table>
  </body>
</html>

...which, when rendered as HTML, looks like this:

enter image description here

The secret sauce of this solution is a recursive named template that sums the products of each <quantity> and <price> combination. This template is used to compute the subtotal for each country and, at the end, the total of all countries. Special thanks to Dimitre Novatchev for this gem (Multiply 2 numbers and then sum).

!That's impressive! Actually my xml file have lots of cd elements.So it is possible that group the same date once again in the table? — Chen, Apr 27 '13 at 04:49
Hey ABach, What made you remove this text from your original answer: "(thanks to Dimitre Novatchev for the nicely put-together template): Multiply 2 numbers and then sum with XSLT)." ? :) — Dimitre Novatchev, Apr 27 '13 at 04:49
Whoops! A long week and a late night; apologies, @DimitreNovatchev! I'll put that back. — ABach, Apr 27 '13 at 04:52
@Chen - I'm not sure what you mean. Could you update your question with the required XML output? — ABach, Apr 27 '13 at 04:54

How to group parent attribute by using Muenchian Method?

2 Answers2