ER characteristics
7 entities
XML Schema
DEEP
Q1: Return the patient name who has a medical history="allergy"
DEEP, AF, SHALLOW
For $a in //patient[medical_history = "allergy"]
Return $a/name/text()
EN
For $a in //{c1}[patient[{c1}medical_history = "allergy"]
Return $a/{c1}name/text()
MCMR
For $a in //{c2}patient[{c2}medical_history = "allergy"]
Return $a/{c2}name/text()
DR
For $a in //{c2}patient[{c2}medical_history = "allergy"]
Return $a/{c2}name/text()
Q2: Return the amount of the all reimbursement of each insurance company
DEEP
For $a in //insurance_company
Let $b := for $c in //reimburse
Where $c/ insurance_company /@id = $a/@id
Return $c/amount
Return $a, $b
AF , SHALLOW
For $a in //insurance_company
Let $b:= for $c in //reimburse
Where $a/@id = $c/@idref
Return $c/amount
Return $a, $b
EN, MCMR
For $a in //{c2}insurance_company
Let $b := $a/{c2}/reimburse/{c2}amount
Return $a, $b
DR
For $a in //{c1}insurance_company
Let $b in $a/{c1}reimburse/{c1}amount
Return $a, $b
Q3: Return the id of insurance company whose amount reimburse is at least twice as high as the initial deductible
DEEP
For $a in //reimburse
Where $a/deductible * 2 <= $a/amount
Return distinct-values ($a/insurance_company/@id)
AF, SHALLOW
For $a in //insurance_company
For $b in //reimburse
Where $a/@id = $b/@idref and $b/deductible * 2 <= $b/amount
Return distinct-values($a/@id)
EN,MCMR
For $a in //{c2}insurance_company
Where $a/{c2}reimburse/{c2}deductible * 2 <= $a/{c2}amount
Return distinct-values($a/@id)
DR
For $a in //{c1}insurance_company
Where $a/{c1}reimburse/{c1}deductible * 2 <= $a/{c1}amount
Return distinct-values($a/@id)
Q4: List the name/address of insurance company where a certain patient got the reimbursement before another. (open_auction/person = insurance_company /patient/account)
DEEP
For $a in //patient[@id = "1"]//reimburse
For $b in //patient[@id = "2"]//reimburse
Where $a/date < $b/date
Return $a/ins.com/name, $a/insurance_company /address
AF
For $a in //patient[@id = "1"]//patient_account
For $b in //patient[@id = "2"]//patient_account
For $c in //insurance_company
Where $a/reimburse/date < $b/reimburse/date and $a/reimburse/@idref = $c/@id
Return $c/name, $c/address
SHALLOW
For $a in //patient[@id = "1"]
For $b in //patient[@id = "2"]
For $c in //patient_account
For $d in //patient_account
For $e in //insurance_company
Where $a/has2/@idref = $c/@id
And $b/has2/@idref = $d/@id
And $c/reimburse/@idref = $e/@id
And $c/reimburse/date < $d/reimburse/date
Return $e/name, $e/address
EN, MCMR
For $a in //{c1}patient[@id = "1"]//{c1}patient_account
For $b in //{c1}patient[@id = "1"]//{c1}patient_account
For $c in //{c2}insurance_company
Where $a/{c1}reimburse/{c1}date < $b/{c1}reimburse/{c1}date
And $a/{c1}reimburse = $c/{c2}reimburse
Return $c/{c2}name, $c/{c2}address
DR
For $a in //{c2}patient[@id = "1"]//{c2}patient_account
For $b in //{c2}patient[@id = "1"]//{c2}patient_account
For $c in //{c1}insurance_company
Where $a/{c2}reimburse/{c2}date < $b/{c2}reimburse/{c2}date
And $a/{c2}reimburse = $c/{c2}reimburse
Return $c/{c1}name, $c/{c1}address
Q5: How many procedure fee cost more than $100
DEEP,AF,SHALLOW
COUNT (For $a in //procedures
Where $a/fee > 100
Return $a)
EN,DR,MCMR
COUNT (For $a in //{c1}procedures
Where $a/{c1}fee > 100
Return $a)
Q6: How many insurance claims are for each patient bill?
DEEP,AF
For $a in //patient_bill//
Return COUNT($a//insurance_claim)
SHALLOW
For $a in //patient_bill
Return COUNT($a/generate)
EN ,MCMR
For $a in //{c1}patient_bill//
Return COUNT($a//{c1}insurance_claim)
DR
For $a in //{c3}patient_bill//
Return COUNT($a//{c3}insurance_claim)
Q7: How many proses (names) are in our database (//name of physician, name of patient...)
DEEP,AF,SHALLOW
For $a in //
Return count($a//physician/name) + count($a/patient/name) + count($a/patient_account/name)
EN,MCMR
For $a in //
Return count($a//{c1}physician/{c1}name) + count($a/{c1}patient/{c1}name) + count($a/{c1}patient_account/{c1}name)
DR
For $a in //
Return count($a//{c1}physician/{c1}name) + count($a/{c2}patient/{c2}name) + count($a/{c2}patient_account/{c2}name)
Q8: List the name of patient and the number of bills they had since registered.
DEEP
For $a in //patient
Let $b := $a//patient_bill
Return $a, count(distinct-values($b))
AF
For $a in //patient
Let $b := For $c in //procedure
For $d in //patient_bill
Where $c/undergoes/@idref = $a/@id and $c/list/@idref = $d/@id
Return ($d)
Return $a, count($b)
SHALLOW
For $a in //patient
Let $b := For $c in //procedure
For $d in //patient_bill
Where $d/list/@idref = $c/@id
And $a/undergoes/@idref = $c/@id
Return ($d)
Return $a, count($b)
EN
For $a in //{c2}patient
Let $b := For $c in //{c1}procedure
For $d in //{c2}patient_bill
Where $c/{c1}undergoes = $a/{c2}undergoes and $c/{c1}list = $d/{c2}list
Return ($d)
Return $a, count($b)
MCMR
For $a in //{c2}patient
Let $b := For $c in //{c1}procedure
For $c in $a//{c2}procedure
For $d in //{c2}patient_bill
Where $c/{c1}list = $d/{c2}list
Return ($d)
Return $a, count($b)
DR
For $a in //{c2}patient
Let $b := For $c in //{c3}patient_bill
Where $a/{c2}//procedure = $c//{c3}procedure
Return $c
Return $a, count($b)
Q9: For each patient, list number of bills generated whose claim policy category = "old citizen" (join patient/procedure, patient bill, insurance claim)
DEEP
For $a in //patient
Let $b := $a//patient_bill
Where $b//insurance_claim/policy_category = "old citizen"
Return $a, count(distinct-values($b))
AF
For $a in //patient
Let $b := For $c in //procedure
For $d in //patient_bill
Where $c/undergoes/@idref = $a/@id and $c/list/@idref = $d/@id
And $d//insurance_claim/policy_category = "old citizen"
Return $d
Return $a, count($b)
SHALLOW
For $a in //patient
Let $b := For $c in //procedure
For $d in //patient_bill
For $e in //insurance_claim
Where $d/list/@idref = $c/@id
And $a/undergoes/@idref = $c/@id
And $e/policy_category = "old citizen"
And $e/@id = $d/generate/@idref
Return ($d)
Return $a, count($b)
EN
For $a in //{c2}patient
Let $b := For $c in //{c1}procedure
For $d in //{c2}patient_bill
Where $c/{c1}undergoes = $a/{c2}undergoes and $c/{c1}list = $d/{c2}list and $d//{c1}insurance_claim/{c1}policy_category = "old citizen"
Return ($d)
Return $a, count($b)
MCMR
For $a in //{c2}patient
Let $b := For $c in //{c1}procedure
For $c in $a//{c2}procedure
For $d in //{c2}patient_bill
Where $c/{c1}list = $d/{c2}list
and $d//{c1}insurance_claim/{c1}policy_category = "old citizen"
Return ($d)
Return $a, count($b)
DR
For $a in //{c2}patient
Let $b := For $c in //{c3}patient_bill
Where $a/{c2}//procedure = $c//{c3}procedure
And $c//{c3}insurance_claim/{c3}policy_category = "old citizen"
Return ($c)
Return $a, count($b)
Q10: List all patients according to their patient’s account’s attribute.
DEEP,AF
For $a in DISTINCT-VALUES( //patient_acccount/agerange)
Let $b := For $c in //patient
Where $c//patient_account/agerange = $a
Return <patient>$c/name</patient>
Return <agerange>
<age>$a/text()</age>
$b
</agerange>
SHALLOW
For $a in DISTINCT-VALUES(//patient_acccount/agerange)
Let $b := For $c in //patient
For $d in //patient_account
Where $d/agerange = $a
And $c/has2/@idref = $d/@id
Return <patient>$c/name</patient>
Return <agerange>
<age>$a/text()</age>
$b
</agerange>
EN, MCMR
For $a in DISTINCT-VALUES(//{c1}patient_acccount/{c1}agerange)
Let $b := For $c in //{c1}patient
Where $c//{c1}patient_account/{c1}agerange = $a
Return <patient>$c/{c1}name</patient>
Return <agerange>
<age>$a/text()</age>
$b
</agerange>
DR
For $a in DISTINCT-VALUES(//{c2}patient_acccount/{c2}agerange)
Let $b := For $c in //{c2}patient
Where $c//{c2}patient_account/{c2}agerange = $a
Return <patient>$c/{c2}name</patient>
Return <agerange>
<age>$a/text()</age>
$b
</agerange>
Q11: For each patient, list the number of patient bill generated to claim from insurance company that the stock value do not exceed 200% of patient’s income (patient (ins. com->claim) (bill->claim))
DEEP
For $a in //patient
Let $b := $a//patient_bill
Where $b//insurance_company/stock_value < 2 * $a/income
Return $a, count(distinct-values($b))
AF
For $a in //patient
Let $b := For $c in //procedure
For $d in //patient_bill
For $e in //insurance_company
Where $c/undergoes/@idref = $a/@id and $c/list/@idref = $d/@id
And $d//sento/@idref = $e/@id and $e/stock_value < 2*$a/income
Return ($d)
Return $a, count($b)
SHALLOW
For $a in //patient
Let $b := For $c in //procedure
For $d in //patient_bill
For $e in //insurance_company
For $f in //insurance_claim
Where $a/undergoes/@idref = $c/@id and $d/list/@idref = $c/@id
And $f//sento/@idref = $e/@id and $e/stock_value < 2*$a/income
And $f/@id = $d/generate/@idref
Return ($d)
Return $a, count($b)
EN
For $a in //{c2}patient
Let $b := For $c in //{c1}procedure
For $d in //{c2}patient_bill
For $e in //{c2}insurance_company
Where $c/{c1}undergoes = $a/{c2}undergoes and $c/{c1}list = $d/{c2}list and $d//{c1} sentto = $e/{c2}sentto and $e/{c2}stock_value < 2*$/{c2}income
Return ($d)
Return $a, count($b)
MCMR
For $a in //{c2}patient
Let $b := For $c in //{c1}procedure
For $c in $a//{c2}procedure
For $d in //{c2}patient_bill
For $e in //{c2}insurance_company
Where $c/{c1}list = $d/{c2}list
and $d//{c1} sentto = $e/{c2}sentto and $e/{c2}stock_value < 2*$a/{c2}income
Return ($d)
Return $a, count($b)
DR
For $a in //{c2}patient
Let $b := For $c in //{c3}patient_bill
For $d in //{c1}insurance_company
Where $a/{c2}//procedure = $c//{c3}procedure
And $c//{c3}/sentto = $d/{c1}sentto and
$d/{c1}stock_value < 2*$/{c2}income
Return ($c)
Return $a, count($b)
Q12: For richer patient, list the number of patient bill generated to claim from insurance company that the stock value do not exceed 200% of patient’s income
DEEP
For $a in //patient[income > 5000]
Let $b := $a//patient_bill
Where $b//insurance_company/stock_value < 2 * $a/income
Return $a, count(distinct-values($b))
AF
For $a in //patient[income > 5000]
Let $b := For $c in //procedure
For $d in //patient_bill
For $e in //insurance_company
Where $c/undergoes/@idref = $a/@id and $c/list/@idref = $d/@id
And $d//sento/@idref = $e/@id and $e/stock_value < 2*$a/income
Return ($d)
Return $a, count($b)
SHALLOW
For $a in //patient[income > 5000]
Let $b := For $c in //procedure
For $d in //patient_bill
For $e in //insurance_company
For $f in //insurance_claim
Where $a/undergoes/@idref = $c/@id and $d/list/@idref = $c/@id
And $f//sento/@idref = $e/@id and $e/stock_value < 2*$a/income
And $f/@id = $d/generate/@idref
Return ($d)
Return $a, count($b)
EN
For $a in //{c2}patient[{c2}income > 5000]
Let $b := For $c in //{c1}procedure
For $d in //{c2}patient_bill
For $e in //{c2}insurance_company
Where $c/{c1}undergoes = $a/{c2}undergoes and $c/{c1}list = $d/{c2}list and $d//{c1} sentto = $e/{c2}sentto and $e/{c2}stock_value < 2*$/{c2}income
Return ($d)
Return $a, count($b)
MCMR
For $a in //{c2}patient[{c2}income > 5000]
Let $b := For $c in //{c1}procedure
For $c in $a//{c2}procedure
For $d in //{c2}patient_bill
For $e in //{c2}insurance_company
Where $c/{c1}list = $d/{c2}list
and $d//{c1}sentto = $e/{c2}sentto and $e/{c2}stock_value < 2*$a/{c2}income
Return ($d)
Return $a, count($b)
DR
For $a in //{c2}patient[{c2}income > 5000]
Let $b := For $c in //{c3}patient_bill
For $d in //{c1}insurance_company.
Where $a/{c2}//procedure = $c//{c3}procedure
And $c//{c3}/sentto = $d/{c1}sentto and
$d/{c1}stock_value < 2*$/{c2}income
Return ($c)
Return $a, count($b)
Q13: List no. of patient bill generated by Ins. Company "A"
DEEP
For $a in //patient_bill
Where $a//insurance_company /name = "A"
Return $a/no
AF
For $a in //patient_bill
For $b in //insurance_company[name = "A"]
Where $a//sentto/@idref = $b/@id
Return $a/no
SHALLOW
For $a in //patient_bill
$b in //insurance_claim
$c in //insurance_company
Where $c/@id = $b/sentto/@idref
And $b/@id = $a/generate/@idref
Return $a/no
EN,MCMR
For $a in //{c1}patient_bill
For $b in //{c2}insurance_company [{c2}name = "A"]
Where $a//{c1}sentto= $b/{c2}sentto
Return $a/{c1}no
DR
For $a in //{c3}patient_bill
For $b in //{c1}insurance_company [{c1}name = "A"]
Where $a//{c3}sentto= $b/{c1}sentto
Return $a/{c3}no
Q14: Return all patient bills that description contain word "heart"
DEEP
For $a in distince-values(//patient_bill)
Where CONTAINS($a/desc,"heart")
Return $a
AF,SHALLOW
For $a in //patient_bill
Where CONTAINS($a/desc,"heart")
Return $a
EN,MCMR
For $a in //{c1}patient_bill
Where CONTAINS($a/{c1}desc,"heart")
Return $a
DR
For $a in //{c3}patient_bill
Where CONTAINS($a/{c3}desc,"heart")
Return $a
Q15: Print the procedure code for physician with name = "David"
DEEP
For $a in //procedure
Where $a//physician/name = "David"
Return $a/code
AF
For $a in //physician[name = "David"]
Return $a/procedure/code
SHALLOW
For $a in //procedure
For $b in //physician
Where $b/name = "David"
And $a/@id = $b/order/@idref
Return $a/code
EN, DR,MCMR
For $a in //{c1}physician[{c1}name = "David"]
Return $a/{c1}procedure/{c1}code
Q16: Return the procedure code for physician with one or more characters "a" in name
DEEP
For $a in //procedure
Where CONTAINS($a//physician/name,"a")
Return $a/code
AF
For $a in //physician
Where CONTAINS($a/name,"a")
Return $a/procedure/code
SHALLOW
For $a in //procedure
For $b in //physician
Where CONTAINS($b/name,"a")
And $a/@id = $b/order/@idref
Return $a/code
EN, DR,MCMR
For $a in //{c1}physician
Where CONTAINS($a/{c1}name,"a")
Return $a/{c1}procedure/{c1}code
Q17: Which patient does not have drug history?
DEEP,AF,SHALLOW
For $a in //patient
Where EMPTY ($a/drug_history)
Return $a/name
EN,DR,MCMR
For $a in //{c2}patient
Where EMPTY ($a/{c2}drug_history)
Return $a/{c2}name
Q18: Convert the claim number to claim code (number concatenation insurance company number) (claim/com)
DEEP
For $a in //insurance_claim
For $b in $a//insurance_company
ORDER BY $a/@id
Return $a/@id CONCAT "_" CONCAT $b/@id
AF,SHALLOW
For $a in //insurance_claim
For $b in //insurance_company
Where $a/sentto/@idref = $b/@id
ORDER BY $a/@id
Return $a/@id CONCAT "_" CONCAT $b/@id
EN
For $a in //{c1}insurance_claim
For $b in //{c2}insurance_company
Where $a/{c1}sentto = $b/{c2}sentto
ORDER BY $a/@id
Return $a/@id CONCAT "_" CONCAT $b/@id
MCMR
For $a in //{c2}insurance_company
For $b in $a//{c2}insurance_claim
ORDER BY $b/@id
Return $b/@id CONCAT "_" CONCAT $a/@id
DR
For $a in //{c1}insurance_company
For $b in $a//{c1}insurance_claim
ORDER BY $b/@id
Return $b/@id CONCAT "_" CONCAT $a/@id
Q19: Give an alphabetically ordered list of all patients, along with their address.
DEEP, AF, SHALLOW
For $a in //patient
Sort by $a/name
Return <patient><name>$a/name</name><add>$a/address</add></patient>
EN,DR,MCMR
For $a in //{c2}patient
Sort by $a/{c2}name
Return <patient><name>$a/{c2}name</name><add>$a/{c2}address</add></patient>
Q20: Group patient by their account’s payment (attribute in patient account) and output cardinality of each group.
DEEP,AF
For $a in //patient
Let $b := {for each $c in $a//patient_account
Return $c/payment/text()}
If SUM($b) < 30000
<low> $a/name</low>
If SUM($b) > 30000 and SUM($b) < 100000
<middle>$a/name</middle>
If SUM($b) < 30000
<high> $a/name</high>
SHALLOW
For $a in //patient
Let $b := {for each $c in //patient_account
Where $c/@id = $a/has2/@idref
Return $c/payment/text()}
If SUM($b) < 30000
<low> $a/name</low>
If SUM($b) > 30000 and SUM($b) < 100000
<middle>$a/name</middle>
If SUM($b) < 30000
<high> $a/name</high>
EN,MCMR
For $a in //{c1}patient
Let $b := {for each $c in $a//{c1}patient_account
Return $c/{c1}payment/text()}
If SUM($b) < 30000
<low> $a/{c1}name</low>
If SUM($b) > 30000 and SUM($b) < 100000
<middle>$a/{c1}name</middle>
If SUM($b) < 30000
<high> $a/{c1}name</high>
DR
For $a in //{c2}patient
Let $b := {for each $c in $a//{c2}patient_account
Return $c/{c2}payment/text()}
If SUM($b) < 30000
<low> $a/{c2}name</low>
If SUM($b) > 30000 and SUM($b) < 100000
<middle>$a/{c2}name</middle>
If SUM($b) < 30000
<high> $a/{c2}name</high>
U1: Insert patient bill into DB for the procedure id = 0
DEEP
For $a in //procedure[@id=0] Insert <list><patient_bill>…</></list> after $a
AF
Insert <patient_bill>…</> after $b in //,
For $a in //procedure[@id=0], insert <list bill_ref = recent_id></list> after $a
SHALLOW
Insert <patient_bill><list idref = "0">…</> after $a in //,
EN, MCMR
Insert <patient_bill></> into $a in //{c1},
<list bill_ref = recent_id></list> under $b in //procedure[@id = "0"]{c1}
Insert <patient_bill><list></></> into $a in //{c2}
DR
Insert <patient bill><list><procedure id = "0"> in $a in //{c3},
Insert <list/> to //procedure[@id = 0] {c1}, Insert <list/> to //procedure[@id = "0"] {c2}
U2: Delete the last reimbursement of insurance company id = 1
AF,SHALLOW
Delete $x/last() from {For $a in //reimburse[@idref = "1"]
Return $a
Order by $a/date}
DEEP
Delete subtree $x/last() from {For $a in //reimburse[insurance_company/@id = "1"]
Return $a
Order by $a/date}
EN,MCMR
Delete $x/last() from {For $a in //{c2}insurance_company[@id = "1"]/ {c2}reimburse
Return $a
Order by $a/{c2}date}
(
DR
Delete $x/last() from {For $a in //{c1}insurance_company[@id = "1"]/ {c1}reimburse
Return $a
Order by $a/{c1}date}
U3: Replace patient zipcode with 10400
DEEP,AF, SHALLOW
Replace //patient[@id = "0"]/zipcode/text()
With "10400"
EN ,MCMR
Replace //patient{c1}[@id = "0"]/zipcode{c1}/text()
With "10400"
DR
Replace //patient{c2} [@id = "0"]/zipcode{c2}/text()
With "10400"
U4: If patient account for the patient’s id = 1 exist, change its name to "ABC" otherwise insert a new patient account.
AF, DEEP
If {For $a in //patient[@id = 1]//patient_account return $a}
Then replace $a/name/text()
With "ABC"
Else insert <has2><patient_account></></>
Into //patient[@id = "1"]
SHALLOW
If {For $a in //patient[@id = "1"]/has2
$b in //patient_account
Where $a/@idref = $b/@id
Return $b}
Then replace $b/name/text()
With "ABC"
Else insert <patient_account/>
Into / get @id
Insert <has2 idref = @id>
Into //patient[@id = "1"]
EN, MCMR
If {For $a in //patient[@id = "1"]{c1}//patient_account{c1} return $a}
Then replace $a/name{c1}/text()
With "ABC"
Else insert <has2><patient_account></></>
Into //patient{c1} [@id = "1"]
DR
If {For $a in //{c2}patient[@id = "1"]{c2}//patient_account{c2} return $a}
Then replace $a/name{c2}/text()
With "ABC"
Else insert <has2><patient_account></></>
Into //patient{c2} [@id = "1"]
U5: For all insurance company count number of reimbursement and insert as <count> element (under insurance company)
DEEP (must update to n copies)
For $a in distinct-values(//insurance_company)
For $d in //insurance_company
Let $b := For $c in //reimburse
Where $c/insurance_company = $a
Return $c
Where $d/@id = $a/@id
Insert <count>COUNT($b)</count> as last() into $d
AF,SHALLOW
For $a in //insurance_company
Let $b := //reimburse
Where $a/@id = $b/@idref
Insert <count>COUNT($b)</count> as last() into $a
EN,MCMR
For $a in //{c2}insurance_company
Let $b := $a/{c2}reimburse
Insert <count>COUNT($b)</count> as last() into $a
DR
For $a in //{c1}insurance_company
Let $b := $a/{c1}reimburse
Insert <count>COUNT($b)</count> as last() into $a
U6: Delete all patient account where one of reimbursed company is company id =1
DEEP
For $a in //patient_account
Where $a//insurance_company/@id = "1"
Delete $a
AF
For $a in //patient_account
For $b in //insurance_company[@id = "1"]
Where $a/reimburse/@idref = $b/@id
Delete $a
SHALLOW
For $a in //patient_account
For $b in //insurance_company[@id = "1"]
For $c in //has2
Where $a/reimburse/@idref = $b/@id
And $c/@idref = $a/@id
Delete $a, $c
EN,MCMR
For $a in //{c1}patient_account
For $b in //{c2}insurance_company[@id = "1"]
Where $a/{c1}/reimburse = $b/{c2}reimburse
Delete $a
DR
For $a in //{c2}patient_account
For $b in //{c1}insurance_company[@id = "1"]
Where $a/{c2}/reimburse = $b/{c1}reimburse
Delete $a
U7: Remove patient account name ="David", remove all associate with the patient and ins. company.
DEEP, AF
For $a in //patient[name = "David"]
Delete $a/has2
SHALLOW
For $a in //patient[name = "David"]/has2
For $b in //patient_account
Where $a/@idref = $b/@id
Delete $a, $b
EN,MCMR,DR
For $a in //{c2}patient[{c2}name = "David"]
Delete $a/{c2}has2
U8: For each patient, compute total procedure fee and insert it into patient’s element
DEEP
For $a in //patient
Let $b := $a//procedure
insert <feetotal>SUM ($b/fee)</feetotal> into $a
AF
For $a in //patient
Let $b := //procedure
Where $b/undergoes/@idref = $a/@id
Insert <feetotal>SUM ($b/fee)</feetotal> into $a
SHALLOW
For $a in //patient
Let $b := //procedure
Where $a/undergoes/@idref = $b/@id
Return insert <feetotal>SUM ($b/fee)</feetotal> into $a
EN
For $a in //{c2}patient
Let $b := //{c1}procedure
Where $b/{c1}undergoes = $a/{c2}undergoes
insert <feetotal>SUM ($b/{c1}fee)</feetotal> into $a
MCMR,DR
For $a in //{c2}patient
Let $b := $a//{c2}procedure
insert <feetotal>SUM ($b/{c2}fee)</feetotal> into $a