Mega Code Archive

 

Categories / XML / Xquery

 

Constraining and sorting on aggregated values

File: Data.xml <inventory id="inv0001" date="2008-12-19" loc="USA">   <car model="A" num="0001" quantity="1" color="navy"/>   <car model="B" num="0002" quantity="1" color="red"/>   <car model="B" num="0003" quantity="2" color="red"/>   <car model="C" num="0004" quantity="1" color="white"/>   <car model="C" num="0004" quantity="1" color="gray"/>   <car model="A" num="0001" quantity="1" color="black"/> </inventory> File: Query.xquery let $allcars := doc("Data.xml")//car for $d in distinct-values($allcars/@model) for $n in distinct-values($allcars/@num) let $cars := $allcars[@model = $d and @num = $n] where sum($cars/@quantity) > 1 order by count($cars) return if (exists($cars))        then <group model="{$d}" num="{$n}" numcars="{count($cars)}"                    totQuant="{sum($cars/@quantity)}"/>        else () Output: <?xml version="1.0" encoding="UTF-8"?> <group totQuant="2" num="0003" model="B" numcars="1"/> <group totQuant="2" num="0001" model="A" numcars="2"/> <group totQuant="2" num="0004" model="C" numcars="2"/>