Building Oracle XML Applications

Grouping Repeating Data Using SQL

One of the most common questions developers learning XSLT ask is, “How can I use XSLT to group repeating information?” In this section, we’ll learn how to exploit the database to do the bulk of the grouping and sorting for us, and then use XSLT to format the information. In situations where you don’t have the luxury of formulating the database query yourself—for example, if you receive the XML document from a business partner—you still need to perform grouping and sorting. In the following section, we outline techniques for performing the grouping in your stylesheet, including a state-of-the-art approach that uses XSLT “keys” like database indexes to increase by an order of magnitude the speed of traditional pure XSLT grouping techniques. Either way, by the end of this chapter, you’ll be aware of all the grouping approaches available and which is best for the task at hand.

Multilevel Grouping with CURSOR Expressions

If we are producing XML documents from SQL query results, we can leverage the native grouping functionality provided by Oracle to deliver the results straight from the database grouped the way we want. Let’s start with a simple XSQL page that queries information about software bugs and the developer who is assigned to fix them:

<xsql:query connection="xmlbook" rowset-element="open-bugs" row-element="bug" id-attribute="" tag-case="lower" xmlns:xsql="urn:oracle-xsql" > SELECT ID,ABSTRACT,OWNER,PRIORITY FROM BUG ORDER BY PRIORITY </xsql:query> ...