Grouping problem in XSLT

Tag: xslt Author: zhangping1013 Date: 2009-11-24

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>
      <data>
        <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][email protected]" />
        <xsl:variable name="pdRevId" select="substring-after($pdRevIdTag,'#')" />
        <xsl:variable name="parentlabeltag" select="[email protected]" />
        <tr>
          <td>
            <xsl:text>Parent: </xsl:text>
            <xsl:value-of select="//DOCUMENT/ProductRevision[@id=$pdRevId][email protected]" />
          </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.

Best Answer

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][email protected]"/>

        <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

comments:

Rashmi, That works. Thank you!!! Thanks a lot for helping me. -George
Rashmi, One more question... How can i call the second xsl file in the first file and provide the xml file generated from the fist as an input to it. I tried couple of things but i have not been successful Thanks for any help -George
You can pass the second xslt as an input parameter to the first xslt. Let me know if you need code sample for that too.
Rashmi, Wish you a great New year. could you please send me a code sample for 'pass the second xslt as an input parameter to the first xslt' Thanks again!
Hi George, Happy New Year to you too! To pass an xslt through code you need to use the appropriate class. For e.g. in C# an instance of XsltArgumentList is passed to the XslCompiledTransform.Transform(). The xslt arg will be a name value pair. Consider name as 'driver' and the value as XmlDocument returned from the output of first xslt. In that case your xslt will change too. You should declare the parameter in the xslt: <xsl:param name="driver" /> and your xsl:for-each should use '$driver/root/Data[....'

Other Answer1

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][email protected]"/>
        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>

comments:

When I try your suggestion using parent title, I get the following result... Parent: TopLevelAsy 1 Parent: Top Assembly Part19 2 Parent: Top Assembly Part15 6 Parent: Top Assembly Sub Assembly 2 However I need the following output Parent: TopLevelAsy 1 Parent: Top Assembly Part19 2 Parent: Top Assembly Part15 2 Parent: Sub Asy Part15 4 Parent: Top Assembly Sub Asy 2
That's because the title that you are using is not the parent title, but its the title of the current node.
In your case, SubAssembly #id18 and SubAssembly #id21 are different instances and ideally you should not group them. If they are not different but the same, then you should have only one instance in the xml and make sure that all part15 use a single parent id. On the other hand, if it is correct that they are different instances, then why do you want to group them? As per me, your xslt is quite right.
Rashmi, I already got the above result and I am aware that the grouping you showed is the result of the different parent ref. Thats the root of my problem. My problem still remains...I need to group them as below Instance Ref:#id41 Parent: TopLevelAsy 1 Instance Ref:#id15 Parent: SubAsy Part15 4 Instance Ref:#id19 Parent: Top Assembly Part19 2 Instance Ref:#id15 Parent: Top Assembly Part15 2 Instance Ref:#id48 Parent: Top Assembly Sub Assembly 2