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

Grouping via several attributes

If facts are to be designated according to different criteria, a grouping via several attributes is required. Analogous to the relational or the SQL-based presentation by increasing the quantity of grouping attributes in the GROUP BY or SELECT clause, in XQuery a grouping via several attributes can be achived by defining several for clauses for the group formation. If we, for example, assume that in the current example the list of employees additionally has an item representing the gender for each employee, the average age per occupational group and per gender can be determined.

<MedicalPersonnel>
<Doctors>
<Doctor><Name>Naumann</Name>
 <Age>32</Age>
 <Genders>m</Gender> </Doctor>
<Doctor><Name>Shore</Name>
 <Age>27</Age>
 <Gender>w</Gender> </Doctor>
<Doctor><Name>Meier</Name>
 <Age>25</Age>
 <Gender>m</Gender> </Doctor>
</Doctors>
<NursingStaff>
<Nurse><Name>Guldenstern</Name>
 <Age>41</Age>
 <Gender>w</Gender> </Nurse>
<Nurse><Name>Murawitz</Name>
 <Age>65</Age>
 <Gender>w</Gender> </Nurse>
</NursingStaff>
</MedicalPersonnel>

The appropriate FLWOR expression would be as follows:

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

In this way, each further grouping criterion is characterised by a new iteration variable in a FLWOR expression. Furthermore, the semantics of the "nested loops" for the grouping via several attributes implies that, in principle, all possible combinations are generated, irrespective of whether at least one item exists for the respective combination in the output data stock. In order to simulate the "classic" grouping semantics and to output only the combinations having at least one item to be aggregated, the fn:exists() function in the where clause is to be used, with the help of which the existence of an item in the sequence to be aggregated is to be tested. As a consequence, the above statement changes into the following expression:

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

 

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

<< backnext >>