transposed xml transaction data display as html table in xslt2

Question

got following xml data, which at a very simple level "simulates" some bank account transactions (withdraws only):

<trnsctns>
 <trnsctn date="19/01/01">
  <OB>100</OB>
  <amnt/>   
 </trnsctn> 
 <trnsctn date="19/03/03">  
  <OB/>
  <amnt>33</amnt>
 </trnsctn>
 <trnsctn date="19/02/02">
  <OB/>
  <amnt>22</amnt>
 </trnsctn>
 <trnsctn date="19/05/05">
  <OB/>
  <amnt>7</amnt>
 </trnsctn>
 <trnsctn date="19/04/04">
  <OB/>
  <amnt>32</amnt> 
 </trnsctn>
</trnsctns>

where amnt is the amount withdrawn through the current month from that initial OB=100 (opening balance); if no amount withdrawn, then the initial balance remains unchanged (like in the first month - where no amount present so the 19/02/02 transaction date OB's value is still 100, and so forth) and the CB (closing balance) - which is a calculated field - has a very simple formula = OB - amnt, with one note though: current CB (calculated field) became the next OB .. The way the result displayed is like this:

<table border="1" style="border-collapse:collapse">
 <tr><th>crrntNmbr</th><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td></tr>
  <tr><th>OB</th><td>100</td><td>100</td><td>78</td><td>45</td><td>13</td></tr> 
  <tr><th>amnt</th><td>-</td><td>22</td><td>33</td><td>32</td><td>7</td></tr>
  <tr><th>CB</th><td>100</td><td>78</td><td>45</td><td>13</td><td>6</td></tr>
 <tr><th>date</th><td>19/01/01</td><td>19/02/02</td><td>19/03/03</td><td>19/04/01</td><td>19/05/05</td></tr>
</table>

Also the fields should be ascending ordered by date (which is in "yy/mm/dd" format - I'd also like to know how to ascending order this dates if they would be formated as "dd/mm/yy"); one could easily notice it's not the same order from the xml data file ...

And the aproximate xslt transformation is as follows (has a lot of errors ... which need to be fixed )

<xsl:template match="/">
 <table border="1" style="border-collapse:collapse">
  <tr><th>crrntNmbr</th>
     <xsl:for-each select="trnsctns/trnsctn">
      <td><xsl:number/></td>
     </xsl:for-each>
   </tr>
  <!-- rows for OB, amount, CB, and date -->
   <xsl:for-each select="trnsctns/trnsctn[1]/*">
     <xsl:variable name="fldNme" select="name()"/> 
      <tr><th><xsl:value-of select="name()"/></th>
       <xsl:for-each select="//trnsctn/*[name()=$fldNme]"> 
         <xsl:variable name="OB" select="trnsctn[position()]/OB"/>
         <xsl:variable name="amt" select="trnsctn[position()]/amnt"/>
         <xsl:variable name="CB" select="$OB - $amt"/> <!-- error ! -->
           <td><xsl:choose>
                <xsl:when test="name() = 'OB'">
                 <xsl:value-of select="."/>
                </xsl:when>
                <xsl:when test="name() = 'amnt'">
                  <xsl:value-of select="."/>
                 </xsl:when>
                 <xsl:when test="name() = 'date'">
                  <xsl:value-of select="."/>
                 </xsl:when>
                 <xsl:otherwise>
                  <xsl:value-of select="$CB"/>  <!-- not good also .. -->
                 </xsl:otherwise>
                </xsl:choose>
            </td>
           </xsl:for-each>
          </tr>
        </xsl:for-each>
      </table>
    </xsl:template>

xslt 2, please help me with this .. Slightly updated thanks in advance ..

Answer 1

I haven't been able to understand your requirements fully enough to give a proper answer, but let me help you to remove some of the faults in your XSLT code.

   <xsl:for-each select="//trnsctn/*[name()=$fldNme]"> 
     <xsl:variable name="OB" select="trnsctn[position()]/OB"/>

The for-each selects an element that is a child of a trnsctn element, and the xsl:variable selects a child of that child whose name is trsnsctn. That isn't going to match anything in the data you have shown us.

When the expression in a filter A[B] is an integer, it means A[position()=B]. So if you write A[position()], it means A[position()=position()] which is always true.

<td><xsl:choose>
     <xsl:when test="name() = 'OB'">
        <xsl:value-of select="."/>
     </xsl:when>

An xsl:choose that branches on the name of the element means you're going out of your way to avoid using the key feature that gives XSLT its power, namely template rules.

Answer 2

There are too many issues here for a single question.

Try this as your starting point; it shows how you can sort the transactions (assuming the format is DD/MM/YY), then process them sequentially and also handle an empty amnt:

XSLT 2.0

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

<xsl:template match="/trnsctns">
    <table border="1">
        <tr>
            <th>date</th>
            <th>ob</th>
            <th>amnt</th>
            <th>cb</th>
        </tr>
        <xsl:call-template name="process">
            <xsl:with-param name="transactions" as="element()*">
                <xsl:perform-sort select="trnsctn">
                    <xsl:sort select="replace(@date, '(.{2})/(.{2})/(.{2})', '$3$2$1')"/>
                </xsl:perform-sort> 
            </xsl:with-param>
        </xsl:call-template>
    </table>
</xsl:template>

<xsl:template name="process">
    <xsl:param name="transactions"/>
    <xsl:param name="tx" select="$transactions[1]" />
    <xsl:param name="ob" select="$tx/OB" />
    <xsl:variable name="amnt" select="$tx/amnt" />
    <xsl:variable name="amnt" select="if ($amnt/text()) then number($amnt) else 0" />
    <xsl:variable name="cb" select="$ob - $amnt" />
    <tr>
        <td>
            <xsl:value-of select="$tx/@date"/>
        </td>   
        <td>
            <xsl:value-of select="$ob"/>
        </td>
        <td>
            <xsl:value-of select="$amnt"/>
        </td>
        <td>
            <xsl:value-of select="$cb"/>
        </td>
    </tr>
    <xsl:if test="count($transactions) > 1">
        <xsl:call-template name="process">
            <xsl:with-param name="transactions" select="$transactions[position() > 1]"/>
            <xsl:with-param name="ob" select="$cb"/>
        </xsl:call-template>
    </xsl:if>   
</xsl:template>

</xsl:stylesheet>

Result (rendered):