Grouping problem in XSLT

I need to group the following XML doc to show:

Parent          Item           Qty
----------------------------------
                TopLevelAsy      1
SubAsy          Part15           4
Top Assembly    Part19           2
Top Assembly    Part15           2
Top Assembly    SubAsy           2 

But what I get using my XSL is:

Parent          Item           Qty
----------------------------------
                TopLevelAsy      1
SubAsy          Part15           2
SubAsy          Part15           2
Top Assembly    Part19           2
Top Assembly    Part15           2
Top Assembly    SubAsy           2 

Her is my XML:

<DOCUMENT>
  <ProductRevision id="id41" name="Top Assembly" accessRefs="#id30" subType="ItemRevision" masterRef="#id47" revision="A"></ProductRevision>
  <ProductRevision id="id15" name="PartA-15" accessRefs="#id30" subType="ItemRevision" masterRef="#id36" revision="A"></ProductRevision>
  <ProductRevision id="id19" name="PartB-19" accessRefs="#id30" subType="ItemRevision" masterRef="#id46" revision="A"></ProductRevision>
  <ProductRevision id="id48" name="SubAsy" accessRefs="#id30" subType="ItemRevision" masterRef="#id76" revision="A"></ProductRevision>
  <ProductView id="id4" ruleRefs="#id2" rootRefs="id7" primaryOccurrenceRef="id7">
    <Occurrence id="id7" instancedRef="#id41" occurrenceRefs="id15 id11 id17 id16 id18 id21">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/"></ApplicationRef>
      <data>
        <title>TopLevelAsy</title>
        <year>1985</year>
      </data>
    </Occurrence>
    <Occurrence id="id11" instancedRef="#id19" parentRef="#id7">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/yBEAAAV4xLJc5D/"></ApplicationRef>
      <data>
        <title>Part19</title>
        <year>1988</year>
      </data>
    </Occurrence>
    <Occurrence id="id15" instancedRef="#id15" parentRef="#id7">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/sdljfjdkLJc5D/"></ApplicationRef>
      <data>
        <title>Part15</title>
        <year>1988</year>
      </data>
    </Occurrence>
    <Occurrence id="id17" instancedRef="#id19" parentRef="#id7">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/yBEAAAV4xLJc5D/"></ApplicationRef>
      <data>
        <title>Part19</title>
        <year>1988</year>
      </data>
    </Occurrence>
    <Occurrence id="id16" instancedRef="#id15" parentRef="#id7">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/sdljfjdkLJc5D/"></ApplicationRef>
      <data>
        <title>Part15</title>
        <year>1988</year>
      </data>
    </Occurrence>
    <!-- sub assembly Second occurrence -->
    <Occurrence id="id21" instancedRef="#id48" parentRef="#id7" occurrenceRefs="id153 id135">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/wesdjdLJc5D/"></ApplicationRef>
      <dat开发者_StackOverflowa>
        <title>Sub Assembly</title>
        <year>1985</year>
      </data>
    </Occurrence>
    <Occurrence id="id153" instancedRef="#id15" parentRef="#id21">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/wesdjdLJc5D/jkdsdwV4xLJc5D/"></ApplicationRef>
      <data>
        <title>Part15</title>
        <year>1988</year>
      </data>
    </Occurrence>
    <Occurrence id="id135" instancedRef="#id15" parentRef="#id21">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/wesdjdLJc5D/jkdsdwV4xLJc5D/"></ApplicationRef>
      <data>
        <title>Part15</title>
        <year>1988</year>
      </data>
    </Occurrence>
    <!-- sub assembly first occurrence -->
    <Occurrence id="id18" instancedRef="#id48" parentRef="#id7" occurrenceRefs="id53 id35">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/wesdjdLJc5D/"></ApplicationRef>
      <data>
        <title>Sub Assembly</title>
        <year>1985</year>
      </data>
    </Occurrence>
    <Occurrence id="id53" instancedRef="#id15" parentRef="#id18">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/wesdjdLJc5D/vdsfdwV4xLJc5D/"></ApplicationRef>
      <data>
        <title>Part15</title>
        <year>1988</year>
      </data>
    </Occurrence>
    <Occurrence id="id35" instancedRef="#id15" parentRef="#id18">
      <ApplicationRef application="CAD" label="i9BAAAV4xLJc5D/wesdjdLJc5D/vdsfdwV4xLJc5D/"></ApplicationRef>
      <data>
        <title>Part15</title>
        <year>1988</year>
      </data>
    </Occurrence>
  </ProductView>
</DOCUMENT>

The XSLT i have written is

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" indent="no" />
  <!--   <xsl:key name="byref" match="Occurrence" use="@instancedRef"/> -->
  <xsl:key name="byid" match="Occurrence" use="concat(@title,@instancedRef)" />
  <xsl:key name="byRef" match="Occurrence" use="@instancedRef" />
  <xsl:template match="/">
    <table border="1">
      <!-- generate the keys for instance occurance-->
      <!-- generate the keys for parent id -->
      <xsl:for-each select="DOCUMENT/ProductView/Occurrence[generate-id(.)=generate-id(key('byid', concat(@instancedRef,@title))[1])]">
        <xsl:sort select="@parentRef" />
        <xsl:variable name="pRef" select="@parentRef" />
        <xsl:variable name="instRef" select="@instancedRef" />
        <xsl:variable name="pdOccId" select="substring-after($pRef,'#')" />
        <xsl:variable name="pdRevIdTag" select="//DOCUMENT/ProductView/Occurrence[@id=$pdOccId]/@instancedRef" />
        <xsl:variable name="pdRevId" select="substring-after($pdRevIdTag,'#')" />
        <xsl:variable name="parentlabeltag" select="ApplicationRef/@label" />
        <tr>
          <td>
            <xsl:text>Parent: </xsl:text>
            <xsl:value-of select="//DOCUMENT/ProductRevision[@id=$pdRevId]/@name" />
          </td>
          <td align="right">
            <xsl:value-of select="data/title" />
            <xsl:text> </xsl:text>
          </td>
          <td>
            <xsl:value-of select="count(key('byid', concat(@instancedRef,@title)))" />
          </td>
        </tr>
      </xsl:for-each>
    </table>
  </xsl:template>
</xsl:stylesheet>

Any help will be great. Please help me figure this out. Thank you.

George,

Here's the solution:

You need to use 2 xslts for the same. The first xslt will generate an easily parseable xml. The second xslt will use this xml as its input to achieve the desired result.

Xslt1:

Your xml will be the input to this xslt

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes"/>
  <xsl:key name="bytitle" match="Occurrence" use="data/title"/>
  <xsl:template match="/">
    <root>

      <xsl:for-each select="DOCUMENT/ProductView/Occurrence[not(@parentRef)]">
        <xsl:element name="Data">
          <xsl:attribute name="parentTitle" />
          <xsl:attribute name="childTitle">
            <xsl:value-of select="data/title"/>
          </xsl:attribute>
        </xsl:element>
      </xsl:for-each>

      <xsl:for-each select="DOCUMENT/ProductView/Occurrence[generate-id(.)=generate-id(key('bytitle', data/title)[1])]">
        <xsl:sort select="@id"/>

        <xsl:variable name="title" select="data/title" />
        <xsl:variable name="driver" select="//DOCUMENT/ProductView/Occurrence[data/title = $title]/@id"/>

        <xsl:for-each select="//DOCUMENT/ProductView/Occurrence[substring-after(@parentRef,'#') = $driver/.]">

          <xsl:element name="Data">
            <xsl:attribute name="parentTitle">
              <xsl:value-of select="$title"/>
            </xsl:attribute>
            <xsl:attribute name="childTitle">
              <xsl:value-of select="data/title"/>
            </xsl:attribute>
          </xsl:element>

        </xsl:for-each>        
      </xsl:for-each>
    </root>
  </xsl:template>
</xsl:stylesheet>

Output of this xslt:

<?xml version="1.0" encoding="utf-8"?>
<root>
  <Data parentTitle="" childTitle="TopLevelAsy" />
  <Data parentTitle="Sub Assembly" childTitle="Part15" />
  <Data parentTitle="Sub Assembly" childTitle="Part15" />
  <Data parentTitle="Sub Assembly" childTitle="Part15" />
  <Data parentTitle="Sub Assembly" childTitle="Part15" />
  <Data parentTitle="TopLevelAsy" childTitle="Part19" />
  <Data parentTitle="TopLevelAsy" childTitle="Part15" />
  <Data parentTitle="TopLevelAsy" childTitle="Part19" />
  <Data parentTitle="TopLevelAsy" childTitle="Part15" />
  <Data parentTitle="TopLevelAsy" childTitle="Sub Assembly" />
  <Data parentTitle="TopLevelAsy" childTitle="Sub Assembly" />
</root>

Xslt2:

The output of xslt 1 should be the input to this xslt

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" indent="yes"/>
  <xsl:key name="bytitle" match="Data" use="concat(@parentTitle, @childTitle)"/>
  <xsl:template match="/">
    <table border="1">
      <xsl:for-each select="/root/Data[generate-id(.)=generate-id(key('bytitle', concat(@parentTitle, @childTitle))[1])]">
        <xsl:sort select="@parentTitle"/>
        <xsl:variable name="parentTitle" select="@parentTitle"/>
        <xsl:variable name="childTitle" select="@childTitle"/>
        <tr>
          <td>
            <xsl:value-of select="@parentTitle"/>
          </td>
          <td>
            <xsl:value-of select="@childTitle"/>
          </td>
          <td>
            <xsl:value-of select="count(//root/Data[@parentTitle=$parentTitle and @childTitle = $childTitle])"/>
          </td>
        </tr>
      </xsl:for-each>
    </table>
  </xsl:template>
</xsl:stylesheet>

Output:

 TopLevelAsy 1 
Sub Assembly Part15 4 
TopLevelAsy Part19 2 
TopLevelAsy Part15 2 
TopLevelAsy Sub Assembly 2 

George, you are getting 2 rows for sub assembly as there are two elements for sub-assembly with different ids (id21 and id18) and out of the four Part15s, two belong to id21 and two belong to id18.

Since you are grouping according to the parent id, your output seems correct. If you want to have both the sub assembly elements grouped, then you need to specify the parent title rather than parent id in your concatenated key.

I just tweaked your xslt to see the grouping. This is how they are grouped. As you can see the Parent Ref for the 2nd and 3rd rows are different

Instance Ref:#id41 Parent Ref: Parent:  TopLevelAsy  1 
Instance Ref:#id15 Parent Ref:#id18 Parent: SubAsy Part15  2 
Instance Ref:#id15 Parent Ref:#id21 Parent: SubAsy Part15  2 
Instance Ref:#id19 Parent Ref:#id7 Parent: Top Assembly Part19  2 
Instance Ref:#id15 Parent Ref:#id7 Parent: Top Assembly Part15  2 
Instance Ref:#id48 Parent Ref:#id7 Parent: Top Assembly Sub Assembly  2 

Edit

This is not a complete solution but you might have to do something on these lines:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" indent="no"/>
  <!--   <xsl:key name="byref" match="Occurrence" use="@instancedRef"/> -->
  <xsl:key name="bytitle" match="Occurrence" use="data/title"/>
  <xsl:template match="/">
    <table border="1">
      <xsl:for-each select="DOCUMENT/ProductView/Occurrence[generate-id(.)=generate-id(key('bytitle', data/title)[1])]">
        <xsl:sort select="@id"/>
        <xsl:variable name="title" select="data/title" />
        <br />
        Title:<xsl:value-of select="$title"/>
        <xsl:variable name="driver" select="//DOCUMENT/ProductView/Occurrence[data/title = $title]/@id"/>
        Driver:<xsl:value-of select="$driver/."/>
        <!--<xsl:for-each select ="$driver">
          <xsl:value-of select="."/>
        </xsl:for-each>-->
        <xsl:for-each select="//DOCUMENT/ProductView/Occurrence[substring-after(@parentRef,'#') = $driver/.]">
          Child:<xsl:value-of select="data/title"/>
        </xsl:for-each>
      </xsl:for-each>
    </table>
  </xsl:template>
</xsl:stylesheet>