[xquery-talk] Xquery question

Sava Jurisic sjurisic at mrxsolutions.com
Fri May 5 06:16:51 PDT 2006

Hi all,

this is my first post to this group;

I need a help with my XQuery:

My XML file:

<?xml version="1.0" encoding="UTF-8"?>
<bookkeeping date="20060116">
        <record billed="5" category="Supply" drid="2" index="1" pid="786"
qty="1" received="5">
        <record billed="12" category="Supply" drid="2" index="2" pid="527"
qty="2" received="12">
            <desc>Ice Pack</desc>
        <record billed="30" category="Fee" drid="2" index="1" pid="437" qty="1"
            <desc>Subsequent Visit</desc>
        <record billed="30" category="Fee" drid="2" index="1" pid="773" qty="1"
            <desc>Subsequent Visit</desc>
My query:

let $stdate := 20060201
let $enddate := 20060331
for $i in xcollection("/db/data/billing/bookkeeping")/bookkeeping[@date >
$stdate and @date < $enddate]
for $zz in $i//record[@category = "Supply"]
let $pat := collection("/db/data/patientlist")/patients/patient[id eq $zz/@pid]
let $dr := collection("/db/data/users")/users/user[@id eq $zz/@drid]
let $date := $i/@date
order by $date
<info date="{$date}" pid="{$zz/@pid}" patient = "{concat($pat/firstname, " ",
$pat/lastname)}" provider = "{concat($dr/dfirstname, " ", $dr/dlastname)}"
Description = "{$zz/desc}" billed ="{$zz/@billed}" received="{$zz/@received}"
Qty="{$zz/@qty}" PaidBy="{??????????}" />


My question:

I would like to populate the PaidBy with the "payment methods". In other words,
after executing the query, if the returned record is within <Cash> tag, the
PaidBy = "Cash" , if it's within <Visa>, PaidBy ="Visa" ...etc.

I know that the xml doc should be probably differently structured, but it's out
of my reach for now; I have to use the current structure;

Many thanks,

More information about the talk mailing list