[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">
<Cash>
<record billed="5" category="Supply" drid="2" index="1" pid="786"
qty="1" received="5">
<desc>BioFreeze</desc>
<bdate>20060116</bdate>
</record>
</Cash>
<VISA>
<record billed="12" category="Supply" drid="2" index="2" pid="527"
qty="2" received="12">
<desc>Ice Pack</desc>
<bdate>20060116</bdate>
</record>
</VISA>
<Debit>
<record billed="30" category="Fee" drid="2" index="1" pid="437" qty="1"
received="30">
<desc>Subsequent Visit</desc>
<bdate>20060116</bdate>
</record>
</Debit>
<Master>
<record billed="30" category="Fee" drid="2" index="1" pid="773" qty="1"
received="30">
<desc>Subsequent Visit</desc>
<bdate>20060116</bdate>
</record>
</Master>
</bookkeeping>
--------------------------------------------------
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
return
<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,
Sava
More information about the talk
mailing list