XSLT Grouping flat XML

Question

I have a flat XML file that is coming from a database. I need to group the data into a more hierarchical layout using an XSL transformation. I have researched quite a bit and come up with Meunchian grouping as the way to go but can't get it to work.

I am going from:

<Report>
<Data>
    <Row>
        <Field name ="AssessmentID">1</Field>
        <Field name ="Company">Test Company</Field>
        <Field name ="Manager">Bob Smith</Field>
        <Field name ="IssueID">1-1</Field>
        <Field name ="IssueTitle">Security Problem</Field>
        <Field name ="IssueDescription">Some Description</Field>
    </Row>
    <Row>
        <Field name ="AssessmentID">1</Field>
        <Field name ="Company">Test Company</Field>
        <Field name ="Manager">Bob Smith</Field>
        <Field name ="IssueID">1-2</Field>
        <Field name ="IssueTitle">Other Problem</Field>
        <Field name ="IssueDescription">Some Other Description</Field>
    </Row>
</Data>
</Report>

To this:

<Assessments>
<Assessment>
    <AssessmentID>1</AssessmentID>
    <Company>Test Company</Company>
    <Manager>Bob Smith</Manager>
    <Issue>
        <IssueID>1-1</IssueID>
        <IssueTitle>Security Problem</IssueTitle>
        <IssueDescription>Some Description</IssueDescription>
    </Issue>
    <Issue>
        <IssueID>1-2</IssueID>
        <IssueTitle>Other Problem</IssueTitle>
        <IssueDescription>Some Other Description</IssueDescription>
    </Issue>
</Assessment>
</Assessments>

This is the code I've come up with so far:

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
        <xsl:key name="keyAssessmentID" match="Row" use="Field[@name='AssessmentID']"/>
        <xsl:key name="keyIssueID" match="Row" use="Field[@name='IssueID']"/>
    <xsl:template match="/">
        <Assessments>
            <!-- Process each Assessment -->
        <xsl:for-each select="//Row[generate-id(.) = generate-id(key('keyAssessmentID', Field[@name='AssessmentID'])[1])]">
                <!-- Select all the issues belonging to the assessment -->
                    <xsl:variable name ="lngAssessmentID"><xsl:value-of select="Field[@name='AssessmentID']" /></xsl:variable>
                    <xsl:variable name="lstIssue" select="//Row[Field[@name='IssueID']=$lngAssessmentID]" />

                    <!-- show details for Issues in Assessments -->
                    <xsl:call-template name="ShowIssuesInAssessment">
                        <xsl:with-param name="lstIssue" select="$lstIssue" />
                    </xsl:call-template>
        </xsl:for-each>             
        </Assessments>
    </xsl:template>

    <xsl:template name="ShowIssuesInAssessment">
        <xsl:param name="lstIssue" />

        <!-- Show the name of the Assessment currently being processed -->
        <AssessmentID>
            <xsl:value-of select="$lstIssue[1]/Field[@name='AssessmentID']" />
        </AssessmentID>

 <!-- Show IssueID for each Issue in the Assessment -->
 <xsl:for-each select="$lstIssue[generate-id(.) = generate-id(key('keyIssueID', Field[@Name='IssueID'])[1])]">
  <xsl:variable name="lngIssueID" select="Field[@Name='IssueID']" />
  <!-- Show details of each Issue -->
  <Issue>
    <IssueID>
        <xsl:value-of select="$lstIssue[Field[@Name='IssueID']=$lngIssueID]/Field[@Name='IssueID']" />
     </IssueID>
     <IssueTitle>
            <xsl:value-of select="$lstIssue[Field[@Name='IssueID']=$lngIssueID]/Field[@Name='IssueTitle']" />
   </IssueTitle>
   </Issue>
     </xsl:for-each>
     </xsl:template>
    </xsl:stylesheet>

I am working off of this example on CodeProject: here

Thanks all for any assistance!

Answer 1

Brilliant answer by @Tim C, but I thought I would add mine for a slightly different angle. I think the point was that you seemed to be using the keyIssueID key in there as well, which is not really needed, based on your suggested output, you only needed to have a key of AssessmentID

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
  <xsl:key name="keyAssessmentID" match="Row" use="./Field[@name='AssessmentID']"/>
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/">
    <xsl:element name="Assessments">
      <xsl:for-each select="//Row[generate-id(.) = generate-id(key('keyAssessmentID',Field[@name='AssessmentID']))]">
        <xsl:element name="Assesment">
          <xsl:variable name="AssessmentId" select="./Field[@name='AssessmentID']" />
          <xsl:element name="AssessmentId">
            <xsl:value-of select="$AssessmentId"/>
          </xsl:element>
          <xsl:element name="Company">
            <xsl:value-of select="./Field[@name='Company']"/>
          </xsl:element>
          <xsl:element name="Manager">
            <xsl:value-of select="./Field[@name='Manager']"/>
          </xsl:element>
          <xsl:for-each select="key(keyAssessmentID,$AssessmentId)">
            <xsl:element name="Issue">
              <xsl:for-each select="following-sibling::Field[contains(@name,'Issue')]">
                <xsl:element name="{./@name}">
                  <xsl:value-of select="."/>
                </xsl:element>
              </xsl:for-each>
            </xsl:element>
          </xsl:for-each>
        </xsl:element>
      </xsl:for-each>
    </xsl:element>
  </xsl:template>
</xsl:stylesheet>

Answer 2

You've started off correctly by using Muenchian Grouping to get the first occurrence of each AssessmentID

<xsl:for-each select="//Row[generate-id(.) = generate-id(key('keyAssessmentID', Field[@name='AssessmentID'])[1])]">

But to get all the "issues" for an assessment, you should be actually using the key (where $AssessmentId is the variable containing the AssessmentId)

<xsl:apply-templates select="key('keyAssessmentID', $AssessmentId)"/>

I can't see any need to use xsl:call-template here, or to pass in the elements in the group as a parameter. Just use template matching, which is what XSLT is good at. Then in the template that matches Row, you can output the issue details

<xsl:template match="Row">
   <Issue>
      <xsl:apply-templates select="Field[@name='IssueID']"/>
      <xsl:apply-templates select="Field[@name='IssueTitle']"/>
      <xsl:apply-templates select="Field[@name='IssueDescription']"/>
   </Issue>
</xsl:template>

And to save code repetition you can have a single template matching these variable fields

<xsl:template match="Row/*">
   <xsl:element name="{@name}">
      <xsl:value-of select="."/>
   </xsl:element>
</xsl:template>

(This makes use of "Attribute Value Templates" to create the element name based on the value of the @name attribute for the field).

Try this XSLT

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
   <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
   <xsl:key name="keyAssessmentID" match="Row" use="Field[@name='AssessmentID']"/>

   <xsl:template match="/*">
      <Assessments>
         <xsl:for-each select=".//Row[generate-id(.) = generate-id(key('keyAssessmentID', Field[@name='AssessmentID'])[1])]">
            <xsl:variable name="AssessmentId" select="Field[@name='AssessmentID']"/>
            <Assessment>
               <AssessmentID>
                  <xsl:value-of select="$AssessmentId"/>
               </AssessmentID>
               <xsl:apply-templates select="key('keyAssessmentID', $AssessmentId)"/>
            </Assessment>
         </xsl:for-each>
      </Assessments>
   </xsl:template>

   <xsl:template match="Row">
      <Issue>
         <xsl:apply-templates select="Field[@name='IssueID']"/>
         <xsl:apply-templates select="Field[@name='IssueTitle']"/>
         <xsl:apply-templates select="Field[@name='IssueDescription']"/>
      </Issue>
   </xsl:template>

   <xsl:template match="Row/*">
      <xsl:element name="{@name}">
         <xsl:value-of select="."/>
      </xsl:element>
   </xsl:template>
</xsl:stylesheet>

Then, have a read of http://www.jenitennison.com/xslt/grouping/muenchian.html to get a better understanding of Muenchian Grouping.