Search code examples
xmlxslt

transposed xml transaction data display as html table in xslt2

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>

p3

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 ..

Solution

  • 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):

    enter image description here