Muenchian? XSLT to denormalize/pivot/flatten xml file?

Question

Given an input xml file with following structure:

<root>
  <record row="1" col="1" val="1" />
  <record row="1" col="2" val="2" />
  <record row="1" col="3" val="3" />
  <record row="1" col="n" val="4" />
  <record row="2" col="1" val="5" />
  <record row="2" col="3" val="6" />
  <record row="2" col="n" val="7" />
  <record row="n" col="2" val="8" />
  <record row="n" col="3" val="9" />
  <record row="n" col="n" val="10" />
</root>

How can I output the following structure using XSLT?

<root>
  <row id="1">
    <col id="1">1</col>
    <col id="2">2</col>
    <col id="3">3</col>
    <col id="n">4</col>
  </row>
  <row id="2">
    <col id="1">5</col>
    <col id="2"></col>
    <col id="3">6</col>
    <col id="n">7</col>
  </row>
  <row id="n">
    <col id="1"></col>
    <col id="2">8</col>
    <col id="3">9</col>
    <col id="n">10</col>
  </row>
</root>

[Note how all columns are output even if there is no related element in input]

EDIT: I may have caused confusion through the use of numbers and letters in my example. The solution I am looking for needs to handle row and column attributes that are non-numeric.

Would you include an example that better shows your input data? If the @id attributes carry no usable value at all (in the question context at least), just leave them off. — Tomalak, Apr 21 '09 at 15:35
In case column/row ids are non-numeric, there will be no need to "fill gaps", so both presented solutions will even be simpler. — Dimitre Novatchev, Apr 21 '09 at 16:32
You need to re-formulate your question -- best start a new question, providing the more precise definition with non-numeric id values. — Dimitre Novatchev, Apr 21 '09 at 16:33
@Dimitre Novatchev: Filling gaps will still be necessary for table cells, regardless of numeric or non-numeric @row/@col values. My guess is that @col values carry no more than informational value, while @row is used for grouping. — Tomalak, Apr 21 '09 at 17:02
Dmitre and Tomalak - Tx, I think it will be easier to post a new question. — eft, Apr 21 '09 at 17:27

score 3 · Accepted Answer · edited May 23 '17 at 12:22

The answers to this question show possible ways to approach the problem:

xslt: How could I use xslt to create a table with multiple columns and rows?

EDIT: A solution that incorporates the techniques seen in the linked question follows.

I am assuming:

your @row and @col attributes are incrementing numbers that define the position of the record in the table, and they cannot really contain the string "n". As such they are not unique throughout the document, which makes them unsuitable as HTML @id attributes. I substituted them by @title attributes in my output.
there are no implicit empty rows (gaps in @row continuity will not produce empty rows), only implicit empty cells.
every @row and @col combination is unique.

This XSLT 1.0 transformation:

<xsl:stylesheet 
  version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>

  <!-- prepare some keys for later use -->
  <xsl:key name="kRecordsByRow" match="record" use="@row" />
  <xsl:key name="kRecordsByPos" match="record" use="concat(@row, ',', @col)" />

  <!-- find out the highest @col number -->
  <xsl:variable name="vMaxCol">
    <xsl:for-each select="/root/record">
      <xsl:sort select="@col" data-type="number" order="descending" />
      <xsl:if test="position() = 1">
        <xsl:value-of select="@col" />
      </xsl:if>
    </xsl:for-each>
  </xsl:variable>

  <!-- select the <record>s that are the first in their rows -->
  <xsl:variable name="vRows" select="
    /root/record[
      generate-id()
      =
      generate-id(key('kRecordsByRow', @row)[1])
    ]
  " />  

  <!-- output basic table structure -->
  <xsl:template match="/root">
    <table>
      <xsl:for-each select="$vRows">
        <xsl:sort select="@row" data-type="number" />
        <tr title="{@row}">
          <xsl:call-template name="td" />
        </tr>
      </xsl:for-each>
    </table>
  </xsl:template>

  <!-- output the right number of <td>s in each row, empty or not -->
  <xsl:template name="td">
    <xsl:param name="col" select="1" />

    <td title="{$col}">
      <xsl:value-of select="key('kRecordsByPos', concat(@row, ',', $col))/@val" />
    </td>

    <xsl:if test="$col &lt; $vMaxCol">
      <xsl:call-template name="td">
        <xsl:with-param name="col" select="$col + 1" />
      </xsl:call-template>
    </xsl:if>
  </xsl:template>

</xsl:stylesheet>

…when applied to this (slightly modified) input:

<root>
  <record row="1" col="1" val="1" />
  <record row="1" col="2" val="2" />
  <record row="1" col="3" val="3" />
  <record row="1" col="4" val="4" />
  <record row="2" col="1" val="5" />
  <record row="2" col="3" val="6" />
  <record row="2" col="4" val="7" />
  <record row="3" col="2" val="8" />
  <record row="3" col="3" val="9" />
  <record row="3" col="4" val="10" />
</root>

…produces:

<table>
  <tr title="1">
    <td title="1">1</td>
    <td title="2">2</td>
    <td title="3">3</td>
    <td title="4">4</td>
  </tr>
  <tr title="2">
    <td title="1">5</td>
    <td title="2"></td>
    <td title="3">6</td>
    <td title="4">7</td>
  </tr>
  <tr title="3">
    <td title="1"></td>
    <td title="2">8</td>
    <td title="3">9</td>
    <td title="4">10</td>
  </tr>
</table>

Muenchian grouping is used to select the first <record>s of each @row group
an <xsl:key> is used to pinpoint a record by it's position
recursion is used to produce a consistent set of <td>s, independent of the actual existence of a <record> at the named position

A very nice and complete solution! I don't have anything to add to it. :) — Dimitre Novatchev, Apr 21 '09 at 13:32
Thank you. :-) (I guess I could have used the "iterating without recursion" technique I learned from your answer in the question I linked). — Tomalak, Apr 21 '09 at 13:38
Tomalak - Thank you for the solution; however, I may have caused confusion through the use of numbers and letters in my example. The solution I am looking for needs to handle row and column attributes that are non-numeric. — eft, Apr 21 '09 at 15:14
Have you looked at my answer in the question I linked to? The XSLT there does just that. — Tomalak, Apr 21 '09 at 15:32
I did but am confused by the location path patterns. I am going to post a new question. Thanks. — eft, Apr 21 '09 at 17:28
I accepted this answer as it was very helpful and although it didn't answer my question completely the fault was my wording in the question. — eft, Apr 21 '09 at 18:36

score 2 · Answer 2 · answered Apr 21 '09 at 14:07

An XSLT 2.0 solution

This transformation:

<xsl:stylesheet version="2.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="xs"
    >

    <xsl:output omit-xml-declaration="yes" indent="yes"/>

    <xsl:variable name="vDoc" as="document-node()"
     select="/"/>

    <xsl:key name="kColsByRow" match="@col" 
         use="../@row"/>

    <xsl:key name="kRecByRowCol" match="record" 
         use="concat(@row,'+',@col)"/>

    <xsl:template match="/*">
      <root>
        <xsl:for-each-group select="*/@row" 
             group-by=".">
          <xsl:sort select="current-grouping-key()"
               data-type="number"/>

           <xsl:variable name="vRow" 
                select="current-grouping-key()"/>  

           <row idd="{$vRow}">

              <xsl:for-each select=
               "1 to max(key('kColsByRow',$vRow)/xs:integer(.))">

                <col idd="{.}">
                  <xsl:value-of select=
                  "key('kRecByRowCol',
                        concat($vRow,'+',.),
                        $vDoc
                       )
                       /
                        @col
                "
                  />
                </col>
              </xsl:for-each>
           </row>    
        </xsl:for-each-group>
      </root>
    </xsl:template>
</xsl:stylesheet>

when applied on this XML document:

<root>
    <record row="1" col="1" val="1" />
    <record row="1" col="2" val="2" />
    <record row="1" col="3" val="3" />
    <record row="1" col="10" val="4" />
    <record row="2" col="1" val="5" />
    <record row="2" col="3" val="6" />
    <record row="2" col="10" val="7" />
    <record row="10" col="2" val="8" />
    <record row="10" col="3" val="9" />
    <record row="10" col="10" val="10" />
</root>

produces the wanted result:

<root>
   <row idd="1">
      <col idd="1">1</col>
      <col idd="2">2</col>
      <col idd="3">3</col>
      <col idd="4"/>
      <col idd="5"/>
      <col idd="6"/>
      <col idd="7"/>
      <col idd="8"/>
      <col idd="9"/>
      <col idd="10">10</col>
   </row>
   <row idd="2">
      <col idd="1">1</col>
      <col idd="2"/>
      <col idd="3">3</col>
      <col idd="4"/>
      <col idd="5"/>
      <col idd="6"/>
      <col idd="7"/>
      <col idd="8"/>
      <col idd="9"/>
      <col idd="10">10</col>
   </row>
   <row idd="10">
      <col idd="1"/>
      <col idd="2">2</col>
      <col idd="3">3</col>
      <col idd="4"/>
      <col idd="5"/>
      <col idd="6"/>
      <col idd="7"/>
      <col idd="8"/>
      <col idd="9"/>
      <col idd="10">10</col>
   </row>
</root>

Muenchian? XSLT to denormalize/pivot/flatten xml file?

2 Answers2

Linked