XSLT to sum product of two attributes

Question

Hi All

I have the following XML source structure:

<turnovers>
    <turnover repid="1" amount="500" rate="0.1"/>
    <turnover repid="5" amount="600" rate="0.5"/>
    <turnover repid="4" amount="400" rate="0.2"/>
    <turnover repid="1" amount="700" rate="0.05"/>
    <turnover repid="2" amount="100" rate="0.15"/>
    <turnover repid="1" amount="900" rate="0.25"/>
    <turnover repid="2" amount="1000" rate="0.18"/>
    <turnover repid="5" amount="200" rate="0.55"/>
    <turnover repid="9" amount="700" rate="0.40"/>
</turnovers>

I need an XSL:value-of select statement that will return the sum of the product of the rate attribute and the amount attribute for a given rep ID. So for rep 5 I need ((600 x 0.5) + (200 x 0.55)).

Answer 1

The easiest way to do it in XSLT is probably to use programming language bindings, so that you can define your own XPath functions.

Answer 2

This should do the trick, you'll need to do some further work to select the distinct repid's

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

 <xsl:template match="/"> 
  <xsl:variable name="totals">
   <product>
    <xsl:for-each select="turnovers/turnover">
     <repid repid="{@repid}">
         <value><xsl:value-of select="@amount * @rate"/></value>
        </repid>
    </xsl:for-each>
   </product>
  </xsl:variable>
  <totals>
   <total repid="5" value="{sum($totals/product/repid[@repid='5']/value)}"/> 
  </totals>       
 </xsl:template>

</xsl:stylesheet>

Answer 3

In plain XSLT 1.0 you need a recursive template for this, for example:

  <xsl:template match="turnovers">
    <xsl:variable name="selectedId" select="5" />
    <xsl:call-template name="sum_turnover">
      <xsl:with-param name="turnovers" select="turnover[@repid=$selectedId]" />
    </xsl:call-template>
  </xsl:template>

  <xsl:template name="sum_turnover">
    <xsl:param name="total" select="0" />
    <xsl:param name="turnovers"  />
    <xsl:variable name="head" select="$turnovers[1]" />
    <xsl:variable name="tail" select="$turnovers[position()>1]" />
    <xsl:variable name="calc" select="$head/@amount * $head/@rate" />
    <xsl:choose>
      <xsl:when test="not($tail)">
        <xsl:value-of select="$total + $calc" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="sum_turnover">
          <xsl:with-param name="total" select="$total + $calc" />
          <xsl:with-param name="turnovers" select="$tail" />
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

Answer 4

<xsl:stylesheet 
  version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>
  <xsl:template match="/turnovers">
    <val>
      <!-- call the sum function (with the relevant nodes) -->
      <xsl:call-template name="sum">
        <xsl:with-param name="nodes" select="turnover[@repid='5']" />
      </xsl:call-template>
    </val>
  </xsl:template>

  <xsl:template name="sum">  
    <xsl:param name="nodes" />
    <xsl:param name="sum" select="0" />

    <xsl:variable name="curr" select="$nodes[1]" />

    <!-- if we have a node, calculate & recurse -->
    <xsl:if test="$curr">
      <xsl:variable name="runningsum" select="
        $sum + $curr/@amount * $curr/@rate
      " />
      <xsl:call-template name="sum">
        <xsl:with-param name="nodes" select="$nodes[position() &gt; 1]" />
        <xsl:with-param name="sum"   select="$runningsum" />
      </xsl:call-template>
    </xsl:if>

    <!-- if we don't have a node (last recursive step), return sum -->
    <xsl:if test="not($curr)">
      <xsl:value-of select="$sum" />
    </xsl:if>

  </xsl:template>
</xsl:stylesheet>

Gives:

<val>410</val>

The two <xsl:if>s can be replaced by a single <xsl:choose>. This would mean one less check during the recursion, but it also means two additional lines of code.

Answer 5

I'm very new to xslt but how do you know that its xsl 1.0 from the question asked?

I'm assuming that the answer is the xsl 1.0 answer as its has the most up votes and the xsl 2.0 answer has none

Answer 6

In XSLT 1.0 the use of FXSL makes such problems easy to solve:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:f="http://fxsl.sf.net/"
 xmlns:ext="http://exslt.org/common"
 exclude-result-prefixes="xsl f ext"
 >
 <xsl:import href="zipWith.xsl"/>
 <xsl:output method="text"/>

  <xsl:variable name="vMultFun" select="document('')/*/f:mult-func[1]"/>    

    <xsl:template match="/"> 
      <xsl:call-template name="profitForId"/>
    </xsl:template>

    <xsl:template name="profitForId">
      <xsl:param name="pId" select="1"/>

      <xsl:variable name="vrtfProducts">
       <xsl:call-template name="zipWith">
         <xsl:with-param name="pFun" select="$vMultFun"/>
         <xsl:with-param name="pList1" select="/*/*[@repid = $pId]/@amount"/>
         <xsl:with-param name="pList2" select="/*/*[@repid = $pId]/@rate"/>
       </xsl:call-template>
      </xsl:variable>

      <xsl:value-of select="sum(ext:node-set($vrtfProducts)/*)"/>
    </xsl:template>

    <f:mult-func/>
    <xsl:template match="f:mult-func" mode="f:FXSL">
     <xsl:param name="pArg1"/>
     <xsl:param name="pArg2"/>

     <xsl:value-of select="$pArg1 * $pArg2"/>
    </xsl:template>
</xsl:stylesheet>

When this transformation is applied on the originally posted source XML document, the correct result is produced:

310

In XSLT 2.0 the same solution using FXSL 2.0 can be expressed by an XPath one-liner:

sum(f:zipWith(f:multiply(),
          /*/*[xs:decimal(@repid) eq 1]/@amount/xs:decimal(.),
          /*/*[xs:decimal(@repid) eq 1]/@rate/xs:decimal(.)
          )
     )

The whole transformation:

<xsl:stylesheet version="2.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:xs="http://www.w3.org/2001/XMLSchema"
 xmlns:f="http://fxsl.sf.net/"
 exclude-result-prefixes="f xs"
>
 <xsl:import href="../f/func-zipWithDVC.xsl"/>
 <xsl:import href="../f/func-Operators.xsl"/>

 <!-- To be applied on testFunc-zipWith4.xml -->
 <xsl:output omit-xml-declaration="yes" indent="yes"/>

 <xsl:template match="/">
   <xsl:value-of select=
   "sum(f:zipWith(f:multiply(),
              /*/*[xs:decimal(@repid) eq 1]/@amount/xs:decimal(.),
              /*/*[xs:decimal(@repid) eq 1]/@rate/xs:decimal(.)
              )
         )
    "/>
 </xsl:template>
</xsl:stylesheet>

Again, this transformation produces the correct answer:

310

Note the following:

1. The f:zipWith() function takes as arguments a function fun() (of two arguments) and two lists of items having the same length. It produces a new list of the same length, whose items are the result of the pair-wise application of fun() on the corresponding k-th items of the two lists.

2. f:zipWith() as in the expression takes the function f:multiply() and two sequences of corresponding "ammount" and "rate" attributes. The sesult is a sequence, each item of which is the product of the corresponding "ammount" and "rate".

3. Finally, the sum of this sequence is produced.

4. There is no need to write an explicit recursion and it is also guaranteed that the behind-the scenes recursion used within f:zipWith() is never going to crash (for all practical cases) with "stack overflow"

Answer 7

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
      xmlns:xs="http://www.w3.org/2001/XMLSchema"
      exclude-result-prefixes="xs"
      version="2.0">

   <xsl:variable name="repid" select="5" />

   <xsl:template match="/">
      <xsl:value-of select=
         "sum(for $x in /turnovers/turnover[@repid=$repid] return $x/@amount * $x/@rate)"/>
   </xsl:template>

</xsl:stylesheet>

You can do this if you just need the value and not xml.