Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / XSLT
Tip/Trick

Grouping XML with Multiple Columns in XSLT 1.0

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
31 Jul 2020CPOL 10.7K   34  
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.

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

Image 1

Expected Output

Image 2

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

History

  • 31st July, 2020 - Initial version

License

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


Written By
Team Leader
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --