Grouping XML with Multiple Columns in XSLT 1.0
Using XSLT version 1.0 to group XML using Multiple Columns
Using the Code
Sample XML
<?xml version="1.0" encoding="UTF-8"?>
<catalog>
<cd>
<title>Empire Burlesque</title>
<artist>Bob Dylan</artist>
<country>USA</country>
<company>Columbia</company>
<price>10.90</price>
<year>1985</year>
</cd>
<cd>
<title>Hide your heart</title>
<artist>Bonnie Tyler</artist>
<country>UK</country>
<company>CBS Records</company>
<price>9.90</price>
<year>1988</year>
</cd>
<cd>
<title>Greatest Hits</title>
<artist>Dolly Parton</artist>
<country>USA</country>
<company>RCA</company>
<price>9.90</price>
<year>1982</year>
</cd>
<cd>
<title>Still got the blues</title>
<artist>Gary Moore</artist>
<country>UK</country>
<company>Virgin records</company>
<price>10.20</price>
<year>1990</year>
</cd>
<cd>
<title>Eros</title>
<artist>Eros Ramazzotti</artist>
<country>EU</country>
<company>BMG</company>
<price>9.90</price>
<year>1997</year>
</cd>
<cd>
<title>One night only</title>
<artist>Bee Gees</artist>
<country>UK</country>
<company>Polydor</company>
<price>10.90</price>
<year>1998</year>
</cd>
<cd>
<title>Sylvias Mother</title>
<artist>Dr.Hook</artist>
<country>UK</country>
<company>CBS</company>
<price>8.10</price>
<year>1973</year>
</cd>
<cd>
<title>Maggie May</title>
<artist>Rod Stewart</artist>
<country>UK</country>
<company>Pickwick</company>
<price>8.50</price>
<year>1990</year>
</cd>
<cd>
<title>Romanza</title>
<artist>Andrea Bocelli</artist>
<country>EU</country>
<company>Polydor</company>
<price>10.80</price>
<year>1996</year>
</cd>
<cd>
<title>When a man loves a woman</title>
<artist>Percy Sledge</artist>
<country>USA</country>
<company>Atlantic</company>
<price>8.70</price>
<year>1987</year>
</cd>
<cd>
<title>Black angel</title>
<artist>Savage Rose</artist>
<country>EU</country>
<company>Mega</company>
<price>10.90</price>
<year>1995</year>
</cd>
<cd>
<title>1999 Grammy Nominees</title>
<artist>Many</artist>
<country>USA</country>
<company>Grammy</company>
<price>10.20</price>
<year>1999</year>
</cd>
<cd>
<title>For the good times</title>
<artist>Kenny Rogers</artist>
<country>UK</country>
<company>Mucik Master</company>
<price>8.70</price>
<year>1995</year>
</cd>
<cd>
<title>Big Willie style</title>
<artist>Will Smith</artist>
<country>USA</country>
<company>Columbia</company>
<price>9.90</price>
<year>1997</year>
</cd>
<cd>
<title>Tupelo Honey</title>
<artist>Van Morrison</artist>
<country>UK</country>
<company>Polydor</company>
<price>8.20</price>
<year>1971</year>
</cd>
<cd>
<title>Soulsville</title>
<artist>Jorn Hoel</artist>
<country>Norway</country>
<company>WEA</company>
<price>7.90</price>
<year>1996</year>
</cd>
<cd>
<title>The very best of</title>
<artist>Cat Stevens</artist>
<country>UK</country>
<company>Island</company>
<price>8.90</price>
<year>1990</year>
</cd>
<cd>
<title>Stop</title>
<artist>Sam Brown</artist>
<country>UK</country>
<company>A and M</company>
<price>8.90</price>
<year>1988</year>
</cd>
<cd>
<title>Bridge of Spies</title>
<artist>T`Pau</artist>
<country>UK</country>
<company>Siren</company>
<price>7.90</price>
<year>1987</year>
</cd>
<cd>
<title>Private Dancer</title>
<artist>Tina Turner</artist>
<country>UK</country>
<company>Capitol</company>
<price>8.90</price>
<year>1983</year>
</cd>
<cd>
<title>Midt om natten</title>
<artist>Kim Larsen</artist>
<country>EU</country>
<company>Medley</company>
<price>7.80</price>
<year>1983</year>
</cd>
<cd>
<title>Pavarotti Gala Concert</title>
<artist>Luciano Pavarotti</artist>
<country>UK</country>
<company>DECCA</company>
<price>9.90</price>
<year>1991</year>
</cd>
<cd>
<title>The dock of the bay</title>
<artist>Otis Redding</artist>
<country>USA</country>
<company>Stax Records</company>
<price>7.90</price>
<year>1968</year>
</cd>
<cd>
<title>Picture book</title>
<artist>Simply Red</artist>
<country>EU</country>
<company>Elektra</company>
<price>7.20</price>
<year>1985</year>
</cd>
<cd>
<title>Red</title>
<artist>The Communards</artist>
<country>UK</country>
<company>London</company>
<price>7.80</price>
<year>1987</year>
</cd>
<cd>
<title>Unchain my heart</title>
<artist>Joe Cocker</artist>
<country>USA</country>
<company>EMI</company>
<price>8.20</price>
<year>1987</year>
</cd>
</catalog>
Normal Output
Expected Output
Code Explanation
XSLT <xsl:key>
Below is syntax of xsl:key.
<xsl:key name="name" match="pattern" use="expression"/>
For this grouping, I simply concatenate two columns in expression. Below is generic sample of Key with two columns.
<xsl:key name="keyCombined" match="MatchingColumn" use="concat(Column1, ' ', Column2)" />
Below is changed Key based on above XML.
<xsl:key name="keyCountryCompany" match="cd" use="concat(country, ' ', company)" />
The below loop selects the first row of each group with unique country and company.
<xsl:for-each select="//cd[generate-id(.) =
generate-id(key('keyCountryCompany', concat(country, ' ', company))[1])]">
The below loop selects all rows with selected country and company from the above loop.
<xsl:variable name="vcountry" select="country" />
<xsl:variable name="vcompany" select="company" />
<xsl:for-each select="//cd[country=$vcountry and company=$vcompany]">
Full Source Code
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:key name="keyCountryCompany" match="cd" use="concat(country, ' ', company)" />
<xsl:template match="/">
<fo:root xmlns:fo="http://www.w3.org/1999/XSL/Format">
<fo:layout-master-set>
<fo:simple-page-master master-name="Test">
<fo:region-body/>
</fo:simple-page-master>
</fo:layout-master-set>
<fo:page-sequence master-reference="Test">
<fo:flow flow-name="xsl-region-body">
<fo:block>
<fo:table border="solid">
<fo:table-column column-number="1" column-width="35%" />
<fo:table-column column-number="2" column-width="35%" />
<fo:table-column column-number="3" column-width="15%" />
<fo:table-column column-number="4" column-width="15%" />
<fo:table-header>
<fo:table-row border="solid" font-weight="bold">
<fo:table-cell>
<fo:block>Title</fo:block>
</fo:table-cell>
<fo:table-cell>
<fo:block>Artist</fo:block>
</fo:table-cell>
<fo:table-cell>
<fo:block>Price</fo:block>
</fo:table-cell>
<fo:table-cell>
<fo:block>Year</fo:block>
</fo:table-cell>
</fo:table-row>
</fo:table-header>
<fo:table-body>
<xsl:for-each select="//cd[generate-id(.) =
generate-id(key('keyCountryCompany', concat(country, ' ', company))[1])]">
<xsl:sort select="country"/>
<xsl:variable name="vcountry" select="country" />
<xsl:variable name="vcompany" select="company" />
<fo:table-row border="solid" font-weight="bold">
<fo:table-cell number-columns-spanned="2">
<fo:block>Country : <xsl:value-of select="$vcountry"/></fo:block>
</fo:table-cell>
<fo:table-cell number-columns-spanned="2">
<fo:block>Company : <xsl:value-of select="$vcompany"/></fo:block>
</fo:table-cell>
</fo:table-row>
<xsl:for-each select="//cd[country=$vcountry and company=$vcompany]">
<xsl:sort select="country"/>
<fo:table-row border="solid">
<fo:table-cell>
<fo:block><xsl:value-of select="title"/></fo:block>
</fo:table-cell>
<fo:table-cell>
<fo:block><xsl:value-of select="artist"/></fo:block>
</fo:table-cell>
<fo:table-cell>
<fo:block><xsl:value-of select="price"/></fo:block>
</fo:table-cell>
<fo:table-cell>
<fo:block><xsl:value-of select="year"/></fo:block>
</fo:table-cell>
</fo:table-row>
</xsl:for-each>
</xsl:for-each>
</fo:table-body>
</fo:table>
</fo:block>
</fo:flow>
</fo:page-sequence>
</fo:root>
</xsl:template>
</xsl:stylesheet>
Grouping with More than 2 Columns
In the above example, I did grouping with two columns. We can use same concatenation in expression for more than two columns based on requirements.
Final Thoughts
If possible, get rid of XSLT 1.0 by migrating to XSLT 2.0 version which has so many features (includes grouping).
Acknowledgements
- The above XML got it from w3schools
- Used Online FOP Render to validate above code & generated PDFs
History
- 31st July, 2020 - Initial version