Grouping XML with Multiple Columns in XSLT 1.0

thatraja

0.00/5 (No votes)

31 Jul 2020CPOL

12.1K

Using XSLT version 1.0 to group XML using Multiple Columns

XSLT 2.0 has for-each-group which can be used for grouping with multiple columns in nested mode. But in 1.0 version, we could achieve this by using Key with expression.

Download source - 15.6 KB

Using the Code

Sample XML

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.

XML

<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.

XML

<xsl:key name="keyCombined" match="MatchingColumn" use="concat(Column1, ' ', Column2)" />

Below is changed Key based on above XML.

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.

XML

<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.

XML

<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

<?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

31^st July, 2020 - Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)