XSL - Sum Amounts

Question

I have built the XSL (v2.0) below to transform a CSV file into an XML file. This bit works great! The part I am stuck on is:

1. Summing up all the values in the "amount" attribute in all "receipt" elements
2. Then populating the sum in the "record_total" attribute in "IMPORT_HEADER"

The XML produced after running the XSLT looks like below, apart from the record_total is not calculated (the bit I can't get to work) ...

<IMPORT_HEADER record_count="3" record_total="175.00" >
<receipt account_code="12345678" amount="25.00" />
<receipt account_code="23456789" amount="50.00" />
<receipt account_code="34567891" amount="100.00" />
</IMPORT_HEADER>

Below is my XSL so far based on my test CSV file containing 3 rows:

    <xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema" >
<xsl:output method="xml" indent="yes"/>

<!-- Parameter used to specify the file location and name of the CSV file -->
<xsl:param name="pathToCSV" select="'file:///c:/csv.csv'" />

<xsl:template match="/">

    <xsl:choose>
    <xsl:when test="unparsed-text-available($pathToCSV)">

        <!-- Read the CSV file and return its contents as a string -->
        <xsl:variable name="csv" select="unparsed-text($pathToCSV)" />

        <!-- Split the csv string into individual rows -->
        <xsl:variable name="rows" select="tokenize($csv, '\r?\n')" /> 

        <!-- Create the root element node and namespace declarations -->        
        <IMPORT_HEADER>

        <!-- Creates the attributes within the root element node -->
        <xsl:attribute name="record_count" select="count($rows)-1"/>
        <xsl:attribute name="record_total" select="'636.13'"/>  

        <!-- Process each row in the CSV file, skip row 1 which contains the column headers -->
        <xsl:for-each select="$rows[position() !=1]">               

            <!-- Split each row into a comma separated list of columns -->
            <xsl:variable name="cols" select="tokenize(., ',')" />

            <!-- Create the child receipt node and populate the attributes -->                          
            <receipt>
                <xsl:attribute name="account_code" select="'12345678'" />
                <xsl:attribute name="amount" select="$cols[12]"/>
            </receipt>      

        </xsl:for-each> 

        </IMPORT_HEADER>

    </xsl:when>
    <xsl:otherwise>
        <xsl:text>Cannot locate : </xsl:text><xsl:value-of select="$pathToCSV" />
    </xsl:otherwise>
    </xsl:choose>   

</xsl:template>

Any help would be massively appreciated!

Signed a XSL novice getting dangerous! :)

Answer 1

You need to create the receipt elements first and store them in a variable, then you can sum the amounts:

    <xsl:variable name="receipts" as="element(receipt)*">
     <!-- Process each row in the CSV file, skip row 1 which contains the column headers -->
     <xsl:for-each select="$rows[position() !=1]">               

        <!-- Split each row into a comma separated list of columns -->
        <xsl:variable name="cols" select="tokenize(., ',')" />

        <!-- Create the child receipt node and populate the attributes -->                          
        <receipt>
            <xsl:attribute name="account_code" select="'12345678'" />
            <xsl:attribute name="amount" select="$cols[12]"/>
        </receipt>      

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

    <IMPORT_HEADER record_count="{count($receipts)}" record_total="sum($receipts/@amount)}">

       <xsl:copy-of select="$receipts"/>  

    </IMPORT_HEADER>