Click here to Skip to main content
14,608,087 members

Grouping XML with Multiple Columns in XSLT 1.0

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

<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 2 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)" />

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])]">

Below Loop selects all rows with selected country and company from 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 above example, I did grouping with 2 columns. We can use same concatenation in expression for more than 2 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

  • 31-July-2020 - Initial Version

License

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

Share

About the Author

thatraja
Web Developer
India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
Tip/Trick
Posted 31 Jul 2020

Tagged as

Stats

1.3K views
16 downloads