XSLT 1. 0 Grouping by two attributes and creating table

Narendra.Patil · September 17th, 2009, 12:53 PM

Hello,

I have XML file like:
<?xml version="1.0"?>
<Body>
<Data ID="1" Country="India" Region="North" SubRegions="A"/>
<Data ID="2" Country="India" Region="North" SubRegions="B"/>
<Data ID="3" Country="India" Region="North" SubRegions="C"/>
<Data ID="4" Country="India" Region="North" SubRegions="D"/>
<Data ID="5" Country="India" Region="South" SubRegions="E"/>
<Data ID="6" Country="India" Region="South" SubRegions="F"/>
<Data ID="7" Country="India" Region="South" SubRegions="G"/>
<Data ID="8" Country="India" Region="South" SubRegions="H"/>
</Body>

I have to create the HTML table having common Country and Region attributes and display all subregions in that combination with table caption as Country-Region
Expected output

<HTML>
<HEAD></HEAD>
<BODY>
<TABLE width="50%" border="1" bordercolor="black" cellpadding="0" cellspacing="0">
<CAPTION>India - North</CAPTION>
<TR>
<TH>ID</TH>
<TH>SubRegion</TH>
</TR>
<TR>
<TD>1</TD>
<TD>A</TD>
</TR>
<TR>
<TD>2</TD>
<TD>B</TD>
</TR>
<TR>
<TD>3</TD>
<TD>C</TD>
</TR>
<TR>
<TD>4</TD>
<TD>D</TD>
</TR>
</TABLE>
<TABLE width="50%" border="1" bordercolor="black" cellpadding="0" cellspacing="0">
<CAPTION>India - South</CAPTION>
<TR>
<TH>ID</TH>
<TH>SubRegion</TH>
</TR>
<TR>
<TD>5</TD>
<TD>E</TD>
</TR>
<TR>
<TD>6</TD>
<TD>F</TD>
</TR>
<TR>
<TD>7</TD>
<TD>G</TD>
</TR>
<TR>
<TD>8</TD>
<TD>H</TD>
</TR>
</TABLE>
</BODY>
</HTML>

India - North
ID SubRegion
1 A
2 B
3 C
4 D
India - South
ID SubRegion
5 E
6 F
7 G
8 H

Can anybody please help me to use XSL Version 1.0

Thanks in advance

Martin Honnen · September 17th, 2009, 01:04 PM

Here is a stylesheet sample, you will need to adjust the table attributes to your needs:

Code:

<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output method="html" indent="yes"/>
  
  <xsl:key name="k1" match="Data" use="concat(@Country, '|', @Region)"/>
  
  <xsl:template match="/">
    <html>
      <head>
        <title>Example</title>
      </head>
      <body>
        <xsl:apply-templates select="Body/Data[generate-id() = generate-id(key('k1', concat(@Country, '|', @Region))[1])]" mode="table"/>
      </body>
    </html>
   </xsl:template>
   
   <xsl:template match="Data" mode="table">
     <table>
       <caption><xsl:value-of select="concat(@Country, ' - ', @Region)"/></caption>
       <thead>
         <tr>
           <th>ID</th>
           <th>SubRegion</th>
         </tr>
       </thead>
       <tbody>
         <xsl:apply-templates select="key('k1', concat(@Country, '|', @Region))"/>
       </tbody>
     </table>
   </xsl:template>
   
   <xsl:template match="Data">
     <tr>
       <td><xsl:value-of select="@ID"/></td>
       <td><xsl:value-of select="@SubRegions"/></td>
     </tr>
   </xsl:template>
  
</xsl:stylesheet>

Narendra.Patil · September 18th, 2009, 04:19 PM

Thank you so much Martin... :)

seshav · September 19th, 2009, 06:26 PM

I need answer for below issue immediately. Plz
I am creating a html table with xslt and need to display data in th erows of table. Part of my xsl code runs like this

<xsl:for-each select="cd/song">
<tr>
<td><xsl:value-of select="."/></td>
</tr>
</xsl:for-each>

My xml file has the multiple song tags data like this:
<cd>
<song>
1, Billie Jean
2. Break my heart
3. Steal love
<song>
<song>
1, Going along
2. Stage fear
3. Now to live
<song>
</cd>
When I open my xml file in IE I get table with rows of data
But data within rows are displayed as
1, Billie Jean2. Break my heart3. Steal love
and in next row has the data
1, Going along2. Stage fear3. Now to live
What I need is to display like with each a row is as I see in xml file like

1, Billie Jean
2. Break my heart
3. Steal love

I tried <xsl:text>
</xsl:text> and also special character values for new line.
<br/> does not solve my problem.
Thank you in advance

Martin Honnen · September 20th, 2009, 06:34 AM

That is more of a HTML problem than an XSLT problem. Browsers collapse white space in element contents. If you don't want that then you can use the 'pre' element and/or CSS to avoid that:

Code:

          <xsl:for-each select="cd/song">
          <tr>
          <td>
            <pre style="white-space: pre;">
              <xsl:value-of select="."/>
            </pre>
          </td>
          </tr>
          </xsl:for-each>

seshav · September 20th, 2009, 01:55 PM

Thank you Martin. that solved my problem. I really appreciate your help. Thanx again

Narendra.Patil · September 22nd, 2009, 03:50 PM

Dear Martin,

I have the XML file :

<?xml version="1.0"?>
<Body>
<Data ID="1" Country="India" Region="North" SubRegions="A"/>
<Data ID="2" Country="India" Region="North" SubRegions="B"/>
<Data ID="3" Country="India" Region="North" SubRegions="C"/>
<Data ID="4" Country="India" Region="North" SubRegions="D"/>
<Data ID="5" Country="India" Region="South" SubRegions="E"/>
<Data ID="6" Country="India" Region="South" SubRegions="F"/>
<Data ID="7" Country="India" Region="South" SubRegions="G"/>
<Data ID="8" Country="India" Region="South" SubRegions="H"/>
</Body>

And I want create report by grouping Regions so that it will VAligned top in one column and the subregion belonging to it in other column
The expected out is like this
<HTML>
<HEAD></HEAD>
<BODY>
<TABLE Border="1">
<TR>
<TH>Region</TH>
<TH>Subregion</TH>
</TR>
<TR>
<TD rowspan="4" valign="top">North</TD>
<TD>A</TD>
</TR>
<TR>
<TD>B</TD>
</TR>
<TR>
<TD>C</TD>
</TR>
<TR>
<TD>D</TD>
</TR>
<TR>
<TD rowspan="4" valign="top">South</TD>
<TD>E</TD>
</TR>
<TR>
<TD>F</TD>
</TR>
<TR>
<TD>G</TD>
</TR>
<TR>
<TD>H</TD>
</TR>
</TABLE>
</BODY>
</HTML>

OUTPUT:
Region Subregion
North A
B
C
D
South E
F
G
H

Can you tell how should be xsl for this?
Thanks in advance

Martin Honnen · September 23rd, 2009, 06:42 AM

Here is an XSLT stylesheet:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:strip-space elements="*"/>
  <xsl:output indent="yes"/>
  
  <xsl:key name="k1" match="Data" use="@Region"/>
  
  <xsl:template match="/">
    <html lang="en">
      <head>
        <title>Example</title>
      </head>
      <body>
        <xsl:apply-templates/>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="Body">
    <table border="1">
      <thead>
        <tr>
          <th>Region</th>
          <th>SubRegion</th>
        </tr>
      </thead>
      <tbody>
        <xsl:apply-templates select="Data[generate-id() = generate-id(key('k1', @Region)[1])]" mode="group"/>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template match="Data" mode="group">
    <tr>
      <td rowspan="{count(key('k1', @Region))}" valign="top">
        <xsl:value-of select="@Region"/>
      </td>
      <td>
        <xsl:value-of select="@SubRegions"/>
      </td>
    </tr>
    <xsl:apply-templates select="key('k1', @Region)[position() &gt; 1]"/>
  </xsl:template>
  
  <xsl:template match="Data">
    <tr>
      <td><xsl:value-of select="@SubRegions"/></td>
    </tr>
  </xsl:template>

</xsl:stylesheet>

Narendra.Patil · September 23rd, 2009, 10:02 AM

Thank You sooo much Martin.. :)

Can we use same xsl for the following output? Grouping two columns Country in one column and Region in other column and Subregion in the last column. Let me know how will be xsl for it

<HTML>
<HEAD></HEAD>
<BODY>
<TABLE Border="1">
<TR>
<TH>Country</TH>
<TH>Region</TH>
<TH>Subregion</TH>
</TR>
<TR>
<TD rowspan="8">India</TD>
<TD rowspan="4" valign="middle">North</TD>
<TD>A</TD>
</TR>
<TR>
<TD>B</TD>
</TR>
<TR>
<TD>C</TD>
</TR>
<TR>
<TD>D</TD>
</TR>
<TR>
<TD rowspan="4" valign="middle">South</TD>
<TD>E</TD>
</TR>
<TR>
<TD>F</TD>
</TR>
<TR>
<TD>G</TD>
</TR>
<TR>
<TD>H</TD>
</TR>
</TABLE>
</BODY>
</HTML>

Country

Region

Subregion

A

B

North

C

India

D

E

South

F

G

H

Martin Honnen · September 23rd, 2009, 12:29 PM

Here is an adapted stylesheet:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:strip-space elements="*"/>
  <xsl:output indent="yes"/>
  
  <xsl:key name="k1" match="Data" use="@Country"/>
  <xsl:key name="k2" match="Data" use="concat(@Country, '|', @Region)"/>
  
  <xsl:template match="/">
    <html lang="en">
      <head>
        <title>Example</title>
      </head>
      <body>
        <xsl:apply-templates/>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="Body">
    <table border="1">
      <thead>
        <tr>
          <th>Country</th>
          <th>Region</th>
          <th>Subregion</th>
        </tr>
      </thead>
      <tbody>
        <xsl:apply-templates select="Data"/>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template match="Data">
    <tr>
      <xsl:variable name="g1" select="key('k1', @Country)"/>
      <xsl:variable name="g2" select="key('k2', concat(@Country, '|', @Region))"/>
      <xsl:if test="generate-id() = generate-id($g1[1])">
        <td rowspan="{count($g1)}">
          <xsl:value-of select="@Country"/>
        </td>
      </xsl:if>
      <xsl:if test="generate-id() = generate-id($g2[1])">
        <td rowspan="{count($g2)}">
          <xsl:value-of select="@Region"/>
        </td>
      </xsl:if>
      <td>
        <xsl:value-of select="@SubRegions"/>
      </td>
    </tr>
  </xsl:template>

</xsl:stylesheet>