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. use a FLWOR statement create a sequence of the distinct categories using the distinct-values() function 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 for each result set in the FLWOR statement return the category name and then all the items in that category Sample Data <items> <item> <name>item #1</name> <category>red</category> </item> <item> <name>item #1</name> <category>red</category> </item> <item> <name>item #2</name> <category>green</category> </item> <item> <name>item #3</name> <category>red</category> </item> <item> <name>item #4</name> <category>blue</category> </item> <item> <name>item #5</name> <category>red</category> </item> <item> <name>item #6</name> <category>blue</category> </item> <item> <name>item #7</name> <category>green</category> </item> <item> <name>item #8</name> <category>red</category> </item> </items> 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. xquery version "1.0"; declare option exist:serialize "method=xhtml media-type=text/html"; (: load the items :) let $data := doc('/db/mdr/apps/training/labs/04-group-by/data.xml')/items let $distinct-categories := distinct-values($data/item/category/text()) return <html> <body> <table border="1"> <thead> <tr> <th>Category</th> <th>Items</th> </tr> </thead> <tbody> { for $category in $distinct-categories return <tr> <td>{$category}</td> <td>{string-join($data/item[category=$category]/name/text(), ', ')}</td> </tr> } </tbody> </table> </body> </html> 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. Sample Output Category Items red item #1, item #3, item #5, item #8 green item #2, item #7 blue item #4, item #6 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.