xml xslt xslt-1.0 xslt-2.0 xslt-grouping

Trying to use XSLT to create HTML table from XML

I'm struggling with XSLT. I'm stuck in procedural land. Basically I have some XML that gets generated from a database that looks a bit like this:

<?xml version="1.0" encoding="iso-8859-1"?>
<report>
    <generated_dtm>2013-03-08T18:57:26+00:00</generated_dtm>

    <range>
        <start_dtm>2013-02-21T17:52:00+00:00</start_dtm>
        <end_dtm>2013-03-08T17:52:00+00:00</end_dtm>
    </range>

    <sensor site_code="A0001" unit_no="1" sensor_no="1">
        <name>Food</name>
        <mu_symbol>°C</mu_symbol>
    </sensor>

    <sensor site_code="A0001" unit_no="1" sensor_no="2">
        <name>Air</name>
        <mu_symbol>°C</mu_symbol>
    </sensor>

    <readings>
        <slot slot_dtm="2013-02-21T17:50:00+00:00">
            <sensor sensor_no="1">
                <v>10</v>
                <status_code>IR</status_code>
                <status_desc>In Range</status_desc>
            </sensor>
            <sensor sensor_no="2">
                <v>20</v>
                <status_code>Lo</status_code>
                <status_desc>Low</status_desc>
                </sensor>
        </slot>

        <slot slot_dtm="2013-02-21T18:00:00+00:00">
            <sensor sensor_no="2">
                <v>21</v>
                <status_code>Lo</status_code>
                <status_desc>Low</status_desc>
            </sensor>
            <sensor sensor_no="1">
                <v>11</v>
                <status_code>IR</status_code>
                <status_desc>In Range</status_desc>
            </sensor>
        </slot>
    </readings>
</report>

I'm trying to end up with the readings in a HTML table, with each sensor being a column, and each row with the time down the left hand side like this:

Time                      | Food | Air
-------------------------------------
2013-02-21T17:50:00+00:00 | 10   | 11
2013-02-21T18:00:00+00:00 | 20   | 22

Although the order of the time-slots is guaranteed to be ascending so I don't need to sort them (there could be 1000's), the problem is that within each time-slot the order of the sensors cannot be guaranteed, so I thought I would loop through the sensors I used to create the table headers each time and select the correct sensor from each slot as I iterate through the slots. Although this doesn't work you'll probably get what I tried to do (I realise now why it doesn't work.. variables do not behave how I expected!) : -

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="report">
  <html>
  <head>
    <title>Report</title>
  </head>
  <body>
      <table border="0" width="100%" bgcolor="#ffffff" cellspacing="0" cellpadding="2">
        <tr>
          <td class="column_head_above" width="70">Time</td>
          <xsl:for-each select="sensor">
            <td class="column_head_above"><xsl:value-of select="name"/><xsl:text> </xsl:text><xsl:value-of select="mu_symbol"/></td>
          </xsl:for-each>
        </tr>

        <!-- go through each time slot -->

        <xsl:for-each select="readings/slot">
          <tr>
            <xsl:variable name="sdtm" select="@slot_dtm" /> 

            <td class="table_data"><xsl:value-of select="$sdtm"/></td>

            <!-- go through each sensor header -->

            <xsl:for-each select="../sensor">
              <xsl:variable name="sno" select="@sensor_no" /> 
              <td>
                <xsl:value-of select="../readings/slot[@slot_dtm=$sdtm]/sensor[@sensor_no=$sno]/v"/>
                <xsl:value-of select="../readings/slot[@slot_dtm=$sdtm]/sensor[@sensor_no=$sno]/status_desc"/>
              </td>
            </xsl:for-each>

          </tr>
        </xsl:for-each>

        <!-- end: go through each time slot -->

      </table>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>

There can be 100's or even 1000's of time-slots, this is just a small example. I can adjust the hierarchy of the XML if it helps, but I cannot put the sensors in order within each time-slot without some serious rework of the database query. I'm hoping that isn't necessary.

Originally I had XML where the slots were separated out like this:

<readings>
    <slot slot_dtm="2013-02-21T17:50:00+00:00">
        <sensor sensor_no="1">
            <v>10</v>
            <status_code>IR</status_code>
            <status_desc>In Range</status_desc>
        </sensor>
    </slot>

    <slot slot_dtm="2013-02-21T17:50:00+00:00">
        <sensor sensor_no="2">
            <v>20</v>
            <status_code>Lo</status_code>
            <status_desc>Low</status_desc>
            </sensor>
    </slot>

    <slot slot_dtm="2013-02-21T18:00:00+00:00">
        <sensor sensor_no="1">
            <v>11</v>
            <status_code>IR</status_code>
            <status_desc>In Range</status_desc>
        </sensor>
    </slot>

    <slot slot_dtm="2013-02-21T18:00:00+00:00">
        <sensor sensor_no="2">
            <v>21</v>
            <status_code>Lo</status_code>
            <status_desc>Low</status_desc>
        </sensor>
    </slot>
</readings>

Which involved a much simpler database query! Here I could guarantee the order, but the XQuery processor I'm using (Qt's QXmlQuery) does not support for-each-group so I could not find a way to group based on time.

Sorry this is so long, I hope someone can help at least point me in the right direction.

Thanks.

Solution

This should do it:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:variable name="allSensors" select="/report/sensor" />

  <xsl:template match="report">
    <html>
      <head>
        <title>Report</title>
      </head>
      <body>
        <table border="0" width="100%" bgcolor="#ffffff"
               cellspacing="0" cellpadding="2">
          <tr>
            <td class="column_head_above" width="70">Time</td>
            <xsl:apply-templates select="sensor" />
          </tr>
          <xsl:apply-templates select="readings/slot" />
        </table>
      </body>
    </html>
  </xsl:template>

  <xsl:template match="report/sensor">
    <td class="column_head_above">
      <xsl:value-of select="concat(name, ' ', mu_symbol)"/>
    </td>
  </xsl:template>

  <xsl:template match="slot">
    <xsl:variable name="currentSensors" select="sensor" />
    <tr>
      <td class="table_data">
        <xsl:value-of select="@slot_dtm"/>
      </td>

      <xsl:apply-templates select="$allSensors/@sensor_no">
        <xsl:with-param name="currentSlot" select="current()/@slot_dtm" />
      </xsl:apply-templates>
    </tr>
  </xsl:template>

  <xsl:template match="@sensor_no">
    <xsl:param name="currentSlot" />

    <td>
      <xsl:variable name="matchingSensor"
                    select="/report/readings/slot[@slot_dtm = $currentSlot]
                            /sensor[@sensor_no = current()]" />
      <xsl:value-of select="concat($matchingSensor/v, ' - ', 
                                       $matchingSensor/status_desc)" />
    </td>
  </xsl:template>
</xsl:stylesheet>

I did some cleanup here, but the main points are:

Keeping a variable reference to the sensor definitions for easy access
Creating a variable reference to the current slot's sensors, which can be referenced inside the for-each.

When run on your sample input, this produces:

<html>
  <head>
    <META http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Report</title>
  </head>
  <body>
    <table border="0" width="100%" bgcolor="#ffffff" cellspacing="0" cellpadding="2">
      <tr>
        <td class="column_head_above" width="70">Time</td>
        <td class="column_head_above">Food °C</td>
        <td class="column_head_above">Air °C</td>
      </tr>
      <tr>
        <td class="table_data">2013-02-21T17:50:00+00:00</td>
        <td>10 - In Range</td>
        <td>20 - Low</td>
      </tr>
      <tr>
        <td class="table_data">2013-02-21T18:00:00+00:00</td>
        <td>11 - In Range</td>
        <td>21 - Low</td>
      </tr>
    </table>
  </body>
</html>