SUM() different nodes of an XML

Question

I have an XML like below in BI Publisher-

<ROW1>
  <TOTAL_RETAIL>10.95</TOTAL_RETAIL>
  <TOTAL_TAX> 1.8</TOTAL_TAX>
  <TOTAL_SHIPPING>7.95</TOTAL_SHIPPING>
</ROW1>

<ROW1> does not repeat. Now as I understand to do a SUM of a node I can use XPath function sum() like <?sum(.//TOTAL_RETAIL)?>. This will sum the values of the node TOTAL_RETAIL only, I want the sum of TOTAL_RETAIL, TOTAL_TAX, and TOTAL_SHIPPING. Is there a way I can write the sum function to achieve this.

Note- It cannot be handled programmatically i.e. using variables etc. since its inside a Report template and has to be defined as <?sum(...)?> this values will be mapped to a specific cell in an excel report template.

Solution 1

Try

<?sum(.//TOTAL_RETAIL | .//TOTAL_TAX | .//TOTAL_SHIPPING)?>

| is the union operator, so the argument you're passing to sum() is the union of the three sub-expressions.

Solution 2

sum(.//TOTAL_RETAIL | .//TOTAL_TAX | .//TOTAL_SHIPPING)

Solution 3

You can also try splitting the sum on each node, it works like this:

<?sum(.//TOTAL_RETAIL) + sum(.//TOTAL_TAX) + sum(.//TOTAL_SHIPPING)?>

And in case you have NULL values or empty nodes, use this filter to make sure that the sum function will always work: sum(.//TOTAL_RETAIL[.!='']) + ...etc

Comments

[AnBisw]

I have a small problem though. When it adds it shows 20.7" rowmark="4 in the cell in the excell report. The cells are formatted as currency other sum done in the same way but on a single column i.e. sum(.//RETAIL) work fine.

[LarsH]

@Annjawn: That sounds like something specific to BI Publisher, about which I know nothing. Maybe it's a bug in BIP. You could try publishing the report in a different format, and see if the result helps to illuminate why the Excel report is acting the way it is.

[LarsH]

@Annjawn: I was assuming, based on your tagging, that the <?sum(...)?> is using the XPath 2.0 sum() function. But a quick search for documentation didn't yield any precise semantics of <?sum(...)?>.

[AnBisw]

It should be using that by default, since sum() is an XPath function. sum() on a single column works fine but the union causes some additional garbage content.

[AnBisw]

I figured out out it to be some weird issue with Excel and resolved it. thanks