How do I produce the required XML output grouping and sorting by like source nodes?

Question

Given this source XML:

<output>
   <report>
      <C01>
         <InvID>001</InvID>
         <Daily>
            <row id="0000">1</row>
            <row id="0001">2</row>
            <row id="0002">5</row>
            <row id="0003">4</row>
         </Daily>
      </C01>
      <C02>
         <InvID>002</InvID>
         <Daily>
            <row id="0000">4</row>
            <row id="0001">3</row>
            <row id="0002">2</row>
            <row id="0003">5</row>
         </Daily>
      </C02>
      <C03>
         <InvID>001</InvID>
         <Daily>
            <row id="0000">3</row>
            <row id="0001">7</row>
            <row id="0002">2</row>
            <row id="0003">4</row>
         </Daily>
      </C03>
   <report>
<output>

How can I produce this output:

<Data invID=001>
   <Value>1</Value>
   <Value>2</Value>
   <Value>5</Value>
   <Value>4</Value>
   <Value>3</Value>
   <Value>7</Value>
   <Value>2</Value>
   <Value>4</Value>
</Data>
<Data invID=002>
   <Value>4</Value>
   <Value>3</Value>
   <Value>2</Value>
   <Value>5</Value>
</Data>

I have been trying to use something like this as the basis, but it feels all wrong:

<xsl:template match="output/report">
   <xsl:for-each select="*[starts-with(name(), 'C') and InvID != '']">
      <xsl:sort select="InvID" />
      <xsl:element name="Data">
         <xsl:attribute name="invID">
            <xsl:value-of select="InvID" />
         </xsl:attribute>
      </xsl:element>
   </xsl:for-each>
</xsl:template>

I need to aggregate all items that share a common InvID, output a single Data element for that group of source elements, and then output all of their row element values into children of that Data element (Value).

Can this be done in XPath 1.0?

Look up _Muenchian grouping_, it's the standard approach for this in XSLT 1.0. — Ian Roberts, Feb 10 '15 at 18:41
Though your expected output looks at odds with your description - you've grouped the C01 and C02 rows together rather than the C01 and C03 ones. And also you'll need to wrap the output in a single top-level element to make it well-formed. — Ian Roberts, Feb 10 '15 at 18:42
@IanRoberts I get the gist of the method, and I read some before. But what I couldn't find is an example of one that _does not_ have a non-static element name. I.e. the examples I find have a fixed node structure, but the main node I need to group around is variable...`C01`, `C02`, `C03`, etc. No idea how to key that... — DonBoitnott, Feb 10 '15 at 18:50

score 1 · Accepted Answer · answered Feb 10 '15 at 19:10

As I suggest in my comment, the technique you're looking for is called Muenchian grouping. This works by defining a key that groups related nodes together, then using a trick with generate-id or count to construct a node set consisting of one "representative" node for each group. In your case, since the nodes you want to group together have different names you have to be a bit more creative with the matching patterns but the principle is the same:

<xsl:key name="groupByInv" match="*[InvID]" use="InvID" />

Here I'm interested in any element (regardless of name) that has an InvID child, and I want to group them together based on the InvID value. Now the actual grouping is straightforward:

<xsl:template match="output/report">
  <xsl:for-each select="*[InvID][generate-id() =
         generate-id(key('groupByInv', InvID)[1])]">
    <Data invID="{InvID}">
      <!-- process all the rows under all the elements in the current group -->
      <xsl:apply-templates select="key('groupByInv', InvID)/Daily/row" />
    </Data>
  </xsl:for-each>
</xsl:template>

<xsl:template match="row">
  <Value><xsl:value-of select="."/></Value>
</xsl:template>

Thank you very much, Ian. I get into XML/XPath so infrequently these days... Works like a charm! — DonBoitnott, Feb 10 '15 at 20:05

How do I produce the required XML output grouping and sorting by like source nodes?

1 Answers1