CODEWITHSUNDEEP
xmlxslt
Scrat
Scrat

Reputation: 99

xsl - sum of n values

From the xml below, I want to take the values in each DESC element, extract only the value before INV and add them all up and print the total to a new element called "Total". In other words, if an element contains:

<DESC>TEST SUPPLIER 1               250600FOO                805415446355485     000000002000.00INV   1234</DESC>

Then I only want to extract:

000000002000.00

and do this for all DESC elements, add them together and output it to an element called Total. The DESC element can appear n number of times. Below is an xml with three occurrences:

<?xml version="1.0" encoding="iso-8859-1"?>
 <root>
  <FOO>OW00007838</FOO>
  <BAR>1</BAR>
  <DESC>TEST SUPPLIER 1               250600FOO                805415446355485     000000002000.00INV   1234</DESC>
  <FOO>OW00007838</FOO>
  <BAR>2</BAR>
  <DESC>TEST SUPPLIER 2               050712ACME                 000256886355485     000000023020.35INV   65870</DESC>
  <FOO>OW00007838</FOO>
  <BAR>4</BAR>
  <DESC>TEST SUPPLIER 3               050712ABCD                          000000698745987     000000049158.68INV   02155</DESC>
</root>

And here is my unfinished stylesheet - I'm using substring to attempt to extract the value, but not sure if it's effective. Was thinking of using a combination of substring-before and -after instead:

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

<xsl:template match="/">
 <Total>
  <xsl:variable name="subTotals"> 
   <xsl:for-each  select="/root/DESC">
    <number>
     <xsl:value-of select="substring(.,86,16)"/>
    </number>
   </xsl:for-each>
  </xsl:variable>
  <xsl:value-of select="sum(exsl:node-set($subTotals)/number)"/>
 </Total>
</xsl:template>

Currently the output only returns 0.

Upvotes: 2

Views: 436

Answers (2)

Rudramuni TP
Rudramuni TP

Reputation: 1278

Try this,

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

<xsl:template match="/">
  <Total>
    <xsl:value-of select="
      sum(/root/DESC/number(tokenize(substring-before(., 'INV'),' ')[last()]))"/>
  </Total>
</xsl:template>

</xsl:stylesheet>

Result:

<Total>74179.03</Total>

Upvotes: 1

samjudson
samjudson

Reputation: 56853

In XSLT you can simply do this:

<xsl:template match="/">
  <Total><xsl:value of select="sum(/root/DESC/number(substring(., 86,16)))</Total>
</xsl:template>

The key is wrapping the substring in number() to make sure it is converted to a number. This may help in your above XSLT 1.0 version as well, I'm not sure.

Also, your data doesn't look valid. In the first DESC to number starts at 76, whereas the last it starts at 87.

Upvotes: 0

Related Questions