XQuery/Grouping Items

Motivation
You have many items in a set of data that have a category associated with them. You want to create a report that sorts the items by a category.

Method
We will perform the query in three steps.
 * 1) use a FLWOR statement create a sequence of the distinct categories using the distinct-values function
 * 2) for each item in the category sequence, select all items that belong to that category.  This will be done by adding a predicate (where clause) to the end of our XPath selector.  This takes the form of data/item[x=y] where if x=y returns true the item will be added to the sequence
 * 3) for each result set in the FLWOR statement return the category name and then all the items in that category

Sample Query
The following XQuery will demonstrate this technique. Note that the distinct values for all the categories are stored in the $distinct-categories variable.

In the query above the statement:

$data/item[category=$category]

reads as "get all the items from the data set that have the category element equal to the current category.

The string-join function just puts a comma and a space string between the items in the output stream for readability.

Discussion
Note that you are not restricted to having an item be in a single category. Adding multiple categories to an item will not require any changes to the script.

You can also add new categories to this list at any time without changing the program above. As long as there are range indexes for the category element the list of all categories will be created very quickly, even for millions of records.