XSLT 1.0 Problems with group and subgrouping data - Muenchian method - nested grouping

Question

I have a xml data export and want to transform the data for further reprocessing, xslt 1.0 is demanded. I know the form is a little bit unusual but this is the required form.

I'm quite new to xsl so I would be really thankful for your help.

This is my xml file:

<?xml version="1.0" encoding="UTF-8"?>
<RESULTSET>
   <RES>
      <NR>1</NR>
      <TYPE >XXX </TYPE>
      <ITEM>A</ITEM>
      <COLOUR>blue</COLOUR>
      <MATERIAL>wood</MATERIAL>
      <COUNTER>1</COUNTER>
    </RES>
   <RES>
      <NR>2</NR>
      <TYPE >YYY </TYPE>
      <ITEM>A</ITEM>
      <COLOUR>red</COLOUR>
      <MATERIAL>plastic</MATERIAL>
      <COUNTER>1</COUNTER>
   </RES>
<RES>
      <NR>2</NR>
      <TYPE >YYY </TYPE>
      <ITEM>C</ITEM>
      <COLOUR>pink</COLOUR>
      <MATERIAL>wood</MATERIAL>
      <COUNTER>3</COUNTER>
   </RES>
   <RES>
      <NR>3</NR>
      <TYPE >ZZZ </TYPE>
      <ITEM>C</ITEM>
      <COLOUR>yellow</COLOUR>
      <MATERIAL>metal</MATERIAL>
      <COUNTER>3</COUNTER>
    </RES>
   <RES>
      <NR>1</NR>
      <TYPE >XXX</TYPE>
      <ITEM>B</ITEM>
      <COLOUR>yellow</COLOUR>
      <MATERIAL>metal</MATERIAL>
      <COUNTER>2</COUNTER>
    </RES>
</RESULTSET>

This is my desired output (rows/columns)

NR	TYPE	ITEM-A	COL-A	MAT-A	ITEM-C	COL-C	MAT-C	ITEM-B	COL-B	MAT-B
1	XXX	A	blue	wood				B	yellow	metal
2	YYY	A	red	plastic	C	pink	wood
3	ZZZ				C	yellow	metal

So I think, I must:

perform a grouping by Nr
perform a grouping by Item
loop through both

What I have until now is this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:key name="res-by-nr" match="RES" use="NR" />
<xsl:template match="RESULTSET">
<th>NR</th>
<th>TYPE</th>
<th>ITEM-A</th>
<th>COL-A</th>
<th>MAT-A</th>
<th>ITEM-B</th>
<th>COL-B</th>
<th>MAT-B</th>
<th>ITEM-C</th>
<th>COL-C</th>
<th>MAT-C</th>
<br></br>
  <xsl:for-each select="RES[count(. | key('res-by-nr', NR)[1]) = 1]">
    <xsl:sort select="NR" />
    <tr>
     <td><xsl:value-of select="NR" /></td>
     <td><xsl:value-of select="TYPE" /></td>
             <xsl:for-each select="key('res-by-nr', NR)">
               <xsl:sort select="Counter" />
             <xsl:choose>
               <xsl:when test = "ITEM='A'">
                     <td><xsl:value-of select="ITEM"/></td>
                     <td><xsl:value-of select="COLOUR"/></td>
                     <td><xsl:value-of select="MATERIAL"/></td>
               </xsl:when>
               <xsl:otherwise>
                     <td>Test</td>
                     <td>Test</td>
                     <td>Test</td>
                </xsl:otherwise>
                </xsl:choose> 
     </xsl:for-each>
  </tr>
  </xsl:for-each>
</xsl:template>
</xsl:stylesheet>

After this part which goes through the different mapped numbers:

<xsl:for-each select="RES[count(. | key('res-by-nr', NR)[1]) = 1]">
    <xsl:sort select="NR" />
    <tr>
     <td><xsl:value-of select="NR" /></td>
     <td><xsl:value-of select="TYPE" /></td>
             <xsl:for-each select="key('res-by-nr', NR)">
               <xsl:sort select="Counter" />

I have to insert somehow a second grouping which goes through the different Items. I tried different things placing a second key etc. but somehow it doesn't work!

I would appreciate any help or comments. Thank a lot!

The example does not disclose the logic that needs to be applied here. What if there are 2 items with the same NR and ITEM? — michael.hor257k, Oct 15 '22 at 19:51
There won't be 2 items with the same NR and ITEM. The export which generates the xml guarantees that the combination Nr and ITEM will be unique. — Leonfauko, Oct 15 '22 at 19:55
Still, the logic is not clear. In your attempt. the column headings are hard-coded, not taken from the input. If that's permissible, then your task can be quite simple - even if a bit tedious. — michael.hor257k, Oct 15 '22 at 20:02
Yes, the headings and the positions of the columns have to be hardcoded in my attempt. My problem is the second grouping by the items, I don't really know where to place it correctly. — Leonfauko, Oct 15 '22 at 20:14
I don't see why grouping by ITEM would be necessary in the given circumstances. — michael.hor257k, Oct 15 '22 at 20:21

michael.hor257k · Answer 1 · 2022-10-26T11:55:29.900

Given that the column heading are permanent and that there can be no duplicate ITEM in a NR group, I believe you could do simply:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:key name="res-by-nr" match="RES" use="NR" />

<xsl:template match="/RESULTSET">
    <table border="1">
        <tr>
            <th>NR</th>
            <th>TYPE</th>
            <th>ITEM-A</th>
            <th>COL-A</th>
            <th>MAT-A</th>
            <th>ITEM-B</th>
            <th>COL-B</th>
            <th>MAT-B</th>
            <th>ITEM-C</th>
            <th>COL-C</th>
            <th>MAT-C</th>
        </tr>
        <xsl:for-each select="RES[count(. | key('res-by-nr', NR)[1]) = 1]">
            <xsl:sort select="NR" />
            <xsl:variable name="current-group" select="key('res-by-nr', NR)" />
            <xsl:variable name="a" select="$current-group[ITEM='A']" />
            <xsl:variable name="b" select="$current-group[ITEM='B']" />
            <xsl:variable name="c" select="$current-group[ITEM='C']" />
            <tr>
                <td>
                    <xsl:value-of select="NR" />
                </td>
                <td>
                    <xsl:value-of select="TYPE" />
                </td>
                <td>
                    <xsl:value-of select="$a/ITEM" />
                </td>
                <td>
                    <xsl:value-of select="$a/COLOUR" />
                </td>
                <td>
                    <xsl:value-of select="$a/MATERIAL" />
                </td>
                <td>
                    <xsl:value-of select="$b/ITEM" />
                </td>
                <td>
                    <xsl:value-of select="$b/COLOUR" />
                </td>
                <td>
                    <xsl:value-of select="$b/MATERIAL" />
                </td>
                <td>
                    <xsl:value-of select="$c/ITEM" />
                </td>
                <td>
                    <xsl:value-of select="$c/COLOUR" />
                </td>
                <td>
                    <xsl:value-of select="$c/MATERIAL" />
                </td>
            </tr>   
        </xsl:for-each> 
    </table>
</xsl:template>

</xsl:stylesheet>

Applied to your input example, this will return:

Result

<?xml version="1.0" encoding="UTF-8"?>
<table border="1">
   <tr>
      <th>NR</th>
      <th>TYPE</th>
      <th>ITEM-A</th>
      <th>COL-A</th>
      <th>MAT-A</th>
      <th>ITEM-B</th>
      <th>COL-B</th>
      <th>MAT-B</th>
      <th>ITEM-C</th>
      <th>COL-C</th>
      <th>MAT-C</th>
   </tr>
   <tr>
      <td>1</td>
      <td>XXX </td>
      <td>A</td>
      <td>blue</td>
      <td>wood</td>
      <td>B</td>
      <td>yellow</td>
      <td>metal</td>
      <td/>
      <td/>
      <td/>
   </tr>
   <tr>
      <td>2</td>
      <td>YYY </td>
      <td>A</td>
      <td>red</td>
      <td>plastic</td>
      <td/>
      <td/>
      <td/>
      <td>C</td>
      <td>pink</td>
      <td>wood</td>
   </tr>
   <tr>
      <td>3</td>
      <td>ZZZ </td>
      <td/>
      <td/>
      <td/>
      <td/>
      <td/>
      <td/>
      <td>C</td>
      <td>yellow</td>
      <td>metal</td>
   </tr>
</table>

rendered as:

Added (1):

If you have many different ITEM values, you can reduce code duplication by doing:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:str="http://exslt.org/strings"
exclude-result-prefixes="str">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:variable name="items" select="str:tokenize('A|B|C', '|')" />
<xsl:variable name="xml" select="/" />

<xsl:key name="res-by-nr" match="RES" use="NR" />
<xsl:key name="res-by-item" match="RES" use="concat(NR, '|', ITEM)" />

<xsl:template match="/RESULTSET">
    <table border="1">
        <tr>
            <th>NR</th>
            <th>TYPE</th>
            <xsl:for-each select="$items">
                <th>
                    <xsl:value-of select="concat('ITEM-', .)" />
                </th>
                <th>
                    <xsl:value-of select="concat('COL-', .)" />
                </th>
                <th>
                    <xsl:value-of select="concat('MAT-', .)" />
                </th>
            </xsl:for-each>
        </tr>
        <xsl:for-each select="RES[count(. | key('res-by-nr', NR)[1]) = 1]">
            <xsl:sort select="NR" />
            <xsl:variable name="nr" select="NR" />
            <tr>
                <td>
                    <xsl:value-of select="NR" />
                </td>
                <td>
                    <xsl:value-of select="TYPE" />
                </td>
                <xsl:for-each select="$items">
                    <xsl:variable name="item" select="." />
                    <!-- switch context back to input XML in order to use key -->
                    <xsl:for-each select="$xml">
                        <xsl:variable name="res" select="key('res-by-item', concat($nr, '|', $item))" />
                        <td>
                            <xsl:value-of select="$res/ITEM" />
                        </td>
                        <td>
                            <xsl:value-of select="$res/COLOUR" />
                        </td>
                        <td>
                            <xsl:value-of select="$res/MATERIAL" />
                        </td>
                    </xsl:for-each> 
                </xsl:for-each> 
            </tr>   
        </xsl:for-each> 
    </table>
</xsl:template>

</xsl:stylesheet>

This is assuming your processor supports the EXSLT str:tokenize() extension function (as Xalan does).

Added (2):

To implement this on the Filemaker platform (i.e. using the Xalan-C processor which does not support the EXSLT str:tokenize() extension function), you can do:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:exsl="http://exslt.org/common"
exclude-result-prefixes="exsl">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:variable name="items-rtf">
    <item>A</item>
    <item>B</item>
    <item>C</item>
    <!-- ... -->
</xsl:variable>
<xsl:variable name="items" select="exsl:node-set($items-rtf)/item" />
<xsl:variable name="xml" select="/" />

<xsl:key name="res-by-nr" match="RES" use="NR" />
<xsl:key name="res-by-item" match="RES" use="concat(NR, '|', ITEM)" />

<xsl:template match="/RESULTSET">
    <table border="1">
        <tr>
            <th>NR</th>
            <th>TYPE</th>
            <xsl:for-each select="$items">
                <th>
                    <xsl:value-of select="concat('ITEM-', .)" />
                </th>
                <th>
                    <xsl:value-of select="concat('COL-', .)" />
                </th>
                <th>
                    <xsl:value-of select="concat('MAT-', .)" />
                </th>
            </xsl:for-each>
        </tr>
        <xsl:for-each select="RES[count(. | key('res-by-nr', NR)[1]) = 1]">
            <xsl:sort select="NR" />
            <xsl:variable name="nr" select="NR" />
            <tr>
                <td>
                    <xsl:value-of select="NR" />
                </td>
                <td>
                    <xsl:value-of select="TYPE" />
                </td>
                <xsl:for-each select="$items">
                    <xsl:variable name="item" select="." />
                    <!-- switch context back to input XML in order to use key -->
                    <xsl:for-each select="$xml">
                        <xsl:variable name="res" select="key('res-by-item', concat($nr, '|', $item))" />
                        <td>
                            <xsl:value-of select="$res/ITEM" />
                        </td>
                        <td>
                            <xsl:value-of select="$res/COLOUR" />
                        </td>
                        <td>
                            <xsl:value-of select="$res/MATERIAL" />
                        </td>
                    </xsl:for-each> 
                </xsl:for-each> 
            </tr>   
        </xsl:for-each> 
    </table>
</xsl:template>

</xsl:stylesheet>

Thanks for the approach, but the xml was just a small extract... I have over 300 different Nrs and over 50 Items. — Leonfauko, Oct 15 '22 at 20:29
Thanks for the approach, but the xml was just a small extract... I have over 300 different Nrs and over 50 Items.So this answer isn't the right solution for my attempt. I think I have to perform the first grouping, and then loop through the ordered items. For example: If the first Item of the grouped Nr. 1 Items is A then place the corresponding name, colour and material, if its not insert three empty td , go to next if next Item is b place the colour, material in the next columns. (with Item A empty this would be column 4,5,6) - Just, don't have a clue how to do it... — Leonfauko, Oct 15 '22 at 20:35
That's good. See if the addition to my answer solves your problem. — michael.hor257k, Oct 15 '22 at 20:59
Thanks a lot! This is what I was looking for! It solves exactly my problem. — Leonfauko, Oct 16 '22 at 04:44
Unfortunately im having some trouble using this stylesheet. I'm using it on a filemaker platform (Xalan Processor) and I'm encountering this errormessage: XalanXPathExceptionError: "The function number http:exslt.org/strings/tokenize' is not available." Is there a workaround? — Leonfauko, Oct 26 '22 at 11:07
[groan] Now you tell me? FileMaker uses the Xalan-C processor, which is different from the standard Xalan processor for Java. I 'll try to add a solution to my answer later. — michael.hor257k, Oct 26 '22 at 11:21
Sorry I didn't know that there is a difference! During the adjustion of the sheet I used the W3 xsl editor, there everything worked out quit well. Today I wanted to implement it for production on the filemaker plattform. This is were I encountered the error message. Thanks a lot for your patience and for coming up with new solutions! — Leonfauko, Oct 26 '22 at 11:38

XSLT 1.0 Problems with group and subgrouping data - Muenchian method - nested grouping

1 Answers1

Added (1):

Added (2):