XQuery problem - fn:sum

[sjurisic]

Hi All,

I hope someone could help; I need to query bunch of files in certain collection in certain period of time and generate the result;

1. All files are stored in //Bookkeeping collection

2. XML file Syntax:

<?xml version="1.0" encoding="UTF-8"?>
<bookkeeping date="20050128">
<Cash>
<record billed="40" category="Fee" drid="2" index="1" pid="15" qty="1" received="20" unitprice="">
<desc>Adjust 40</desc>
<bdate>20050130</bdate>
</record>
<record billed="40" category="Fee" drid="2" index="1" pid="15" qty="1" received="20" unitprice="">
<desc>OST: Adjust 40</desc>
<bdate>20050130</bdate>
</record>
</Cash>
<VISA/>
<Debit/>
<Master/>
<AMEX/>
<Cheque/>
</bookkeeping>

3. My query:

for $b in //bookkeeping
let $m := $b/VISA/record/@billed
let $n := $b/VISA/record/@received
where $b/@date[contains(., '200501')] and $b/VISA/record/@billed > '0'
return
<VISA date="{$b/@date}" billed="{sum($m)}" received="{sum($n)}"/>

4. Current result:

<VISA date="20050112" billed="128.0" received="128.0"/>
<VISA date="20050106" billed="400.0" received="400.0"/>
<VISA date="20050119" billed="60.0" received="60.0"/>
<VISA date="20050125" billed="256.0" received="256.0"/>
<VISA date="20050126" billed="100.0" received="100.0"/>
<VISA date="20050127" billed="60.0" received="60.0"/>
<VISA date="20050131" billed="40.0" received="40.0"/>
<VISA date="20050130" billed="115.0" received="115.0"/>

------------------------------------------------------------------
What I need is:

<VISA billed="3400" received="3400"> - the summary of the above results;

MANY THANKS,

Sava

 

[JontyMC]

Not so good at XQuery, but try this:

let $m := //bookkeeping[@date[contains(., '200501')]]/VISA/record
return
<VISA billed="{sum($m)/@billed}" received="{sum($m/@received)}"/>