XML : Adding values from XML nodes based on first and last dates using XSLT

I have an XML file that has several sibling nodes, one which stores timestamps <TIMESTAMP>, another with categorical codes <Code>, and another with integer values <Count>. I want to calculate the difference in <Count> values, per <Code>, associated with the first and last <TIMESTAMP>. There are multiple codes... I have simplified the XML quite a bit. The result of this calculation will then populate one of two cells in an HTML table. I am restricted to using XSLT 1.0 to transform the XML to HTML.

Here is a snippet of the XML:

XML

  <?xml version="1.0" encoding="UTF-8"?>  <surveyGDB>    <table>      <tablename>QaQcPoints        <record>          <OBJECTID>1</OBJECTID>          <TIMESTAMP>20150722 09:18:43</TIMESTAMP>          <Code>tp</Code>          <Count>50</Count>        </record>        <record>          <OBJECTID>2</OBJECTID>          <TIMESTAMP>20150722 09:18:43</TIMESTAMP>          <Code>bl</Code>          <Count>110</Count>        </record>        <record>          <OBJECTID>5</OBJECTID>          <TIMESTAMP>20150730 01:05:22</TIMESTAMP>          <Code>bl</Code>          <Count>100</Count>        </record>        <record>          <OBJECTID>6</OBJECTID>          <TIMESTAMP>20150730 01:05:22</TIMESTAMP>          <Code>tp</Code>          <Count>55</Count>        </record>      </tablename>    </table>  </surveyGDB>    

The output HTML table should look something like this:

HTML

  ----------------------------  | Code   | Added | Deleted |  ----------------------------  | tp     | 5     | 0       |  ----------------------------  | bl     | 0     | 10      |  ----------------------------    

I assume I need to sort the <TIMESTAMP> values, and find the first and last positions of the sorted values. Here's the relevant XSL code I have for finding the <Count> value associated with the earliest timestamp:

XSL

  <xsl:for-each select="/surveyGDB/table/tablename/record" />      <xsl:sort select="TIMESTAMP" />          <xsl:choose>              <xsl:when test="position() = 1">                  <xsl:value-of select="Count" />              </xsl:when>          </xsl:choose>    

But how do I also find the last timestamp, and then how do I group the <Count> values by the <Code>?

Any advice on how to approach this?

No comments:

Post a Comment