XQuery Flowers
Use XQuery to retrieve and
transform both XML and relational data.
Query is an XML query language developed and standardized by the World
Wide Web Consortium (W3C). XQuery's purpose is to
find, retrieve, and rearrange data viewed through the lens of XML. XQuery is the syntax, and XML expressions are what you
write using that syntax. Oracle's implementation of XQuery
makes its debut in Oracle Database 10g Release 2, enabling you to draw
data from both XML documents and relational tables, and to then use that data
in generating new XML documents and relational rowsets.
A Small Scenario
Suppose you're a database developer and you help maintain a database for
the tourist industry. Figure 1 shows the data you have to work with:
- Chamber-of-commerce data in a
traditional, relational table (CHAMBER_OF_COMMERCE).
- Information about tourist
attractions in XML documents that you access via the Oracle XML DB
Repository. Each document represents one attraction.
- Census data as documents in a
table (COUNTY_CENSUS) in an XMLType column. Each row contains an XML document with population counts for
all cities in a given county.
Your manager has just asked you to generate an XML document combining
census data and information for tourists, organized by county. You need to
- Generate a set of county nodes
from the CHAMBER_OF_COMMERCE table
- Retrieve the attraction data
for each county from XML documents in the Oracle XML DB Repository
- Summarize the population counts
from XML documents in the COUNTY_CENSUS table
Thankfully, you've just upgraded to Oracle Database 10g Release 2,
and XQuery is at your disposal. You can view all your
data, even the chamber-of- commerce data, as XML documents. You can express
your query using a syntax that lets you "think in XML." Manipulating
and rearranging your input XML documents to generate an entirely new type of
XML document is an almost trivial exercise. XQuery
makes an otherwise challenging task quite easy indeed.
Understanding an XQuery
Counties have attractions, and counties have cities with populations. When
joining together many-to-one data sources, I always find it helpful to begin at
the top. Listing 1 shows a simple XML query against the CHAMBER_OF_COMMERCE
table.
Code Listing 1: Demonstrating the ora:view function
SELECT XMLQuery( 'for $c in ora:view("CHAMBER_OF_COMMERCE") return $c' RETURNING CONTENT)FROM dual; <ROW> <COC_ID>1</COC_ID> <COC_NAME>Alger County Chamber of Commerce</COC_NAME> <COC_PHONE>(906) 387-2138</COC_PHONE> <COC_COUNTY>Alger</COC_COUNTY></ROW>
The
XMLQuery
function used in Listing 1 is part of Oracle's XQuery
application-programming interface (API). It's a SQL/XML function that transmits
an XQuery statement to the database and returns a
single XML document as a result. The XML document will be an XMLType
value. The for
and return clauses in the first argument to XMLQuery
represent XQuery syntax. The W3C XQuery
standard requires that XQuery keywords be lowercase,
something to be careful of if you're used to uppercasing your SQL keywords.
Listing
1's XQuery statement invokes the ora:view function built into
Oracle's implementation of XQuery. The function
transforms the rows from the CHAMBER_OF_COMMERCE table into a sequence of
XML documents. Each document is composed of a single <ROW> element
representing one row from the table. Elements corresponding to the column
values for the row are nested under that <ROW> element.
The for clause in Listing 1 iterates over
the incoming XML documents. During each iteration, the variable $c refers to the current
document. No transformation of the XML takes place. The return clause executes for each iteration and simply returns each input document,
unchanged, as an element in the output document. The <ROW> element shown
in Listing 1 reflects the output from ora:view.
You
won't be able to read much about XQuery without encountering the acronym FLWOR.
This acronym is pronounced "flower," and it refers to the basic for-let-where-order by-return expression syntax that
is at the core of working with XQuery. Listing 1 uses for and return.
Expertise