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.

LarsH · Accepted Answer · 2012-08-30T15:51:41.633

8

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.

edited Aug 30 '12 at 15:51

answered Aug 30 '12 at 15:28

LarsH

27,481
8
94
152

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. – Anjan Biswas Aug 30 '12 at 16:01
@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 Aug 30 '12 at 19:22
@Annjawn: I was assuming, based on your tagging, that the `` is using the XPath 2.0 `sum()` function. But a quick search for documentation didn't yield any precise semantics of ``. – LarsH Aug 30 '12 at 19:28
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. – Anjan Biswas Aug 30 '12 at 19:34
I figured out out it to be some weird issue with Excel and resolved it. thanks – Anjan Biswas Aug 30 '12 at 20:45

score 4 · Answer 2 · answered Aug 30 '12 at 15:30

4

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

answered Aug 30 '12 at 15:30

JohnB

13,315
4
38
65

score 3 · Answer 3 · answered May 01 '13 at 06:02

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

SUM() different nodes of an XML

3 Answers3