[xsl] Multi level Grouping using xslt1.0

["Bafna, Kamlesh" <Kamlesh.Bafna@xxxxxxx>]

Hi,

I am trying to transform a sample input xml given below using xslt 1.0
(I cannot move on to XSLT 2.0 as .NET does not support it and I cannot
use other xslt processors)-

<Data>
  <Rows>
    <Row>
      <Company>491</Company>
      <Business>0000</Business>
      <Department>0000</Department>
      <Account>30010</Account>
    </Row>
    <Row>
      <Company>491</Company>
      <Business>0000</Business>
      <Department>0001</Department>
      <Account>30010</Account>
    </Row>
    <Row>
      <Company>498</Company>
      <Business>0000</Business>
      <Department>0000</Department>
      <Account>30010</Account>
    </Row>
    <Row>
      <Company>498</Company>
      <Business>0001</Business>
      <Department>0000</Department>
      <Account>30011</Account>
    </Row>
  </Rows>
</Data>

The requirement is to group by Company, then by Business, then by
Department & finally the Account.

The output expected is -

<Message>
  <Company>
    <Code>491</Code>
    <Business>
      <Code>0000</Code>
      <Department>
        <Code>0000</Code>
        <Account>30010</Account>
      </Department>
      <Department>
        <Code>0001</Code>
        <Account>30010</Account>
      </Department>
    </Business>
  </Company>
  <Company>
    <Code>498</Code>
    <Business>
      <Code>0000</Code>
      <Department>
        <Code>0000</Code>
        <Account>30010</Account>
      </Department>
    </Business>
    <Business>
      <Code>0001</Code>
      <Department>
        <Code>0000</Code>
        <Account>30011</Account>
      </Department>
    </Business>
  </Company>
</Message>

I am using the following xslt -

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>
  <xsl:template match="/">
    <Message>
      <xsl:apply-templates/>
    </Message>
  </xsl:template>

  <xsl:key name="departments-by-company" match="/Data/Rows/Row/Company"
use="text()" />
  <xsl:key name="departments-by-business"
match="/Data/Rows/Row/Business" use="text()" />
  <xsl:key name="departments-by-department"
match="/Data/Rows/Row/Department" use="text()" />
  <xsl:key name="departments-by-account" match="/Data/Rows/Row/Account"
use="text()" />

  <xsl:template match="Rows">
     <xsl:variable name="inputDataRow" select="."/>

    <xsl:for-each select="./Row/Company[generate-id(.) =
generate-id(key('departments-by-company',text()))]">
      <xsl:element name="Company">
        <xsl:variable name="companyValue" select="."/>
        <xsl:element name="Code">
          <xsl:value-of select="$companyValue"/>
        </xsl:element>
          <xsl:for-each
select="$inputDataRow/Row[Company/text()=$companyValue]/Business[generat
e-id(.) = generate-id(key('departments-by-business',text()))]">
            <xsl:element name="Business">
              <xsl:element name="Code">
                <xsl:value-of select="."/>
              </xsl:element>
              <xsl:for-each
select="$inputDataRow/Row/Department[generate-id(.) =
generate-id(key('departments-by-department',text()))]">
                <xsl:element name="Department">
                  <xsl:element name="Code">
                    <xsl:value-of select="."/>
                  </xsl:element>
                  <xsl:for-each
select="$inputDataRow/Row/Account[generate-id(.) =
generate-id(key('departments-by-account',text()))]">
                    <xsl:element name="Account">
                        <xsl:value-of select="."/>
                    </xsl:element>
                  </xsl:for-each>
                </xsl:element>
              </xsl:for-each>
            </xsl:element>
          </xsl:for-each>
      </xsl:element>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

However I am getting the following unexpected output -
The issue is - I am unanble to loop through all the business for each
distinct company and then for all the departments for each distinct
business/company and so on. Any ideas/solution is greatly appreciated.

<?xml version="1.0" encoding="utf-8"?>
<Message>
  <Company>
    <Code>491</Code>
    <Business>
      <Code>0000</Code>
      <Department>
        <Code>0000</Code>
        <Account>30010</Account>
        <Account>30011</Account>
      </Department>
      <Department>
        <Code>0001</Code>
        <Account>30010</Account>
        <Account>30011</Account>
      </Department>
    </Business>
  </Company>
  <Company>
    <Code>498</Code>
    <Business>
      <Code>0001</Code>
      <Department>
        <Code>0000</Code>
        <Account>30010</Account>
        <Account>30011</Account>
      </Department>
      <Department>
        <Code>0001</Code>
        <Account>30010</Account>
        <Account>30011</Account>
      </Department>
    </Business>
  </Company>
</Message>

Thanks
Kamlesh Bafna

--------------------------------------------------------------------------
ITV plc (Registration No. 4967001)
bITVb is incorporated in England and Wales with its registered office at
200 Grays Inn Road, London WC1X 8HF.
Please visit the official ITV website at http://www.itv.com/ for the latest
company news.

The contents of this email and any attachments are confidential and may
contain privileged or copyright information. If you have received this email
and you are not the intended recipient please notify mailto:postmaster@xxxxxxx
and delete this email and you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this email
are strictly prohibited.
Although ITV routinely screens for viruses, recipients should scan this email
and any attachments for viruses. ITV does not certify that this email or any
of its attachments is free of viruses or defects. ITV reserves the right to
monitor all e mails and the systems upon which such e mails are stored or
circulated.

This email does not conclude a binding agreement. Any views or opinions
presented in this email are solely those of the author and do not necessarily
represent those of ITV.

Please consider the environment before printing this email.
Thank You.
--------------------------------------------------------------------------