• +49-(0)721-402485-12
Your experts for XML, XQuery and XML Databases

Nested grouping

XQuery is provided with an extremely mighty feature based on the principle of the application of aggregation functions on sequences, namely the ability of the nesting with groupings. The rationale behind this principle is that all expressions returning a sequence of nodes can be used as a parameter for an aggregation function.

Since apart from simple path expressions also any complex FLWOR expressions represent a XQuery expression, (potentially correlated) FLWOR expressions may appear as parameters of each aggregation function. Without the additional let clause in the outer FLWOR expression, the calculation of the average age could also be done analogously via an additional FLWOR construct within the fn:avg() function as follows:

<MedicalPersonnel>
{
for $o in fn:distinct-values(for $i in fn:doc("...")//
MedicalPersonnel/*/*
return fn:name($i))
return (<OccupationalGroup> {$o} </OccupationalGroup>,
<Age>{ fn:avg(let $x := fn:doc("...")//*
[fn:name(.) = $o]/Age
return $x)} </Age>)
}
</MedicalPersonnel>

Here, the FLWOR expression within the aggregation function is correlated since it references a variable ($o) which refers to a context outside the expression. Analogous to the preceding notation, the let clause is re-evaluated for each iteration of the for clause.

In return clauses as well as via functions, the nesting of FLWOR expressions is based on the object construction in the field of object-oriented query languages and opens a wide range for complex queries. In all cases where, for example, aggregation functions are allowed (for instance in the form of a group filtering within a where clause), a nesting of FLWOR expressions is possible.

The powerfulness of the concept shall be demonstrated by means of the following example, where the average treatment costs per doctor in the entire hospital shall be determined. The treatment costs result form the sum of the medical articles consumed by the doctor during individual treatment procedures.

In a first step, the treatment costs for one doctor are to be calculated per summation. With $d as variable for the doctor currently to be regarded, the total sum for each treatment for which this doctor is responsible results from the multiplication of the amount (from the Patients collection) and the unit price (from Consumables.xml), whereby the equality of the article is used as join criterion. Then, a summation of all costs per performed treatment results in the total costs per doctor:

for $t in fn:collection("Patients")//In-patient/
Treatment[xqb:follow-xlink(../Doctor/@xlink:href) is $d]
return
fn:sum(for $x in $t//ArticleConsumed,
$y in fn:doc("Consumables.xml")//Article
where $x/@Article_id = $y/@ID
return $x/@Amount * $y/UnitPrice)

The average treatment costs per actually attending doctor result from the application of the fn:avg() function on the treatment costs calculated above for each individual doctor. The whole query results in the following expression:

<TreatmentCosts_by_Doctor>
{
for $d in fn:doc("Hochwaldklinik.xml")//Doctor
return
<Doctor>
<Name>{ $d//FirstName, $d//LastName }</Name>
<TreatmentCosts>
{ fn:avg(for $p in fn:collection("Patients")//In-patient/
Treatment[xqb:follow-xlink(../Doctor/@xlink:href) is $d]
return
fn:sum(for $x in $p//ArticleConsumed,
$y in fn:doc("Consumables.xml")
//Article
where $x/@Article_id = $y/@ID
return $x/@Amount * $y/UnitPrice)
) }
</TreatmentCosts>
</Doctor> }
</TreatmentCosts_by_Doctor>

 

Source: "XQuery – Grundlagen und fortgeschrittene Methoden", dpunkt-Verlag, Heidelberg (2004)

<< back