0

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.

Mark
  • 1,296
  • 13
  • 28

2 Answers2

1

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>
JLRishe
  • 99,490
  • 19
  • 131
  • 169
  • Thanks! Since writing the question I'd started getting somewhere with it and am not far off what you have above. I think after going through what you've put closely, it will of clicked. I will put what I've got so far as a separate answer, but I will mark yours as the correct answer. Thanks again :-) – Mark Mar 08 '13 at 20:58
  • Problem: I've updated my version based on your code and it's actually not working. It is probably to do with the XSLT processor I'm using (it's the one in Qt 4.8.4, which I think uses libxml2), but basically as soon as I go into the for-each loop $currentSensors becomes empty, like it doesn't exist. This is basically the problem I was having to begin with, that as soon as I enter a for-each loop anything from the previous scope seems to not exist. Any thoughts? – Mark Mar 09 '13 at 09:15
  • Hmm, that's very odd. Could you try the above modification? I've created a separate template for the `td`s and am passing `$currentSensors` as a parameter. I wonder if the variable will still go ignored in that case. – JLRishe Mar 09 '13 at 09:50
  • I got the following error: Required type is node(), but item() was found. – Mark Mar 09 '13 at 12:24
  • It looks like its the Qt 4.8 XSLT processor. If I use xsltproc (comes with libxslt) it works fine - and if I use the libxml and libxslt in my software instead, that also works fine. It's just a pain as it means yet more dependencies, but it looks like I'll have to stop using Qt for XSLT processing. – Mark Mar 09 '13 at 13:05
  • If you do have any more suggestions though that'd be great. I've learnt a lot from what you've told me. – Mark Mar 09 '13 at 13:05
  • The last ditch idea I have is to change this: `` to this: ``, but the likelihood of that helping is about 10%. If the solution that you posted works in your XSLT processor, does everything you need, and that limitation I mentioned in the comments isn't an issue, then it probably makes sense to use your implementation. – JLRishe Mar 09 '13 at 13:09
  • I've posted one more potential solution above, that passes the datetime as a parameter instead of a nodeset. Could you give that a try? – JLRishe Mar 09 '13 at 13:14
  • Will do, though it's likely I will be using the different XSLT processor otherwise it's going to start getting messy trying to find workarounds. Your original solution was the neatest so it'd be nice to use that. – Mark Mar 09 '13 at 14:57
0

Here's an update after spending some time figuring it out. Thanks to JLRishe for his answer too. Between that and what I've worked out it is starting to become clear (until the next problem!).

<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:apply-templates select="sensors/sensor">
            <xsl:sort select="@sensor_no" />
          </xsl:apply-templates>
        </tr>

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

        <xsl:for-each select="readings/slot">
          <tr>
            <td class="table_data"><xsl:value-of select="@slot_dtm"/></td>

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

            <xsl:apply-templates select="sensor">
              <xsl:sort select="@sensor_no" />
            </xsl:apply-templates>

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

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

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

<xsl:template match="slot/sensor">
    <td>
        <xsl:value-of select="@sensor_no"/> -
        <xsl:value-of select="v"/> -
        <xsl:value-of select="status_desc"/>
    </td>
</xsl:template>

<xsl:template match="sensors/sensor">
    <td class="column_head_above">
        <xsl:value-of select="name"/><xsl:text> </xsl:text><xsl:value-of select="mu_symbol"/>
    </td>
</xsl:template>

</xsl:stylesheet>
Mark
  • 1,296
  • 13
  • 28
  • One limitation to this approach is that you could have missing ``s if any of the `` elements is missing a `` (I don't know if that can happen in your case though). I would suggest adding `data-type="numeric"` to the `xsl:sort`s if the IDs will always be numeric. – JLRishe Mar 09 '13 at 09:54