Test XML queries interactively in Oracle 10gR2 SQL*Plus

by Bob Watkins | Mar 13, 2007 6:45:00 PM

Tags: Bob Watkins, XML Query, XQuery, Oracle Corp., XML

Takeaway: The SQL*Plus program can now process XML (and SQL) queries using the XQUERY command added in Oracle 10gR2. This tip shows you some of the possibilities.

XML has become a common standard for information interchange. XQuery 1.0 is a standard language for querying data expressed in XML form. It is often called the SQL of XML because it performs the same function for XML that the SQL language does for relational data.

The XQUERY command is an addition to SQL*Plus in Oracle 10g, Release 2. It performs XQuery 1.0 searches interactively at the command line. So, instead of using the XMLQUERY function, capturing the results into a variable, and displaying that variable, you can now just type the XQUERY statement and let SQL*Plus do the display.

The format of the command is:

XQUERY xquery_statement

/

The forward slash, on a line by itself, terminates the code of the query just as it does for a SQL query. The xquery_statement can be any valid XQuery 1.0 statement. The two most common types are an XPath expression and a FLWOR (pronounced "flower") expression.

FLWOR (an abbreviation that stands for "For, Let, Where, Order By, Return") is the component parts of an XQuery statement. Listing A shows an example, using the HR sample schema supplied with Oracle.

To find out which jobs have a minimum salary greater than $8000/month, in SQL the query would be:

SELECT * FROM jobs WHERE min_salary > 8000

/

and the result would be returned as a set of relational rows. The equivalent XML query would be:

XQUERY for $i in ora:view("JOBS") 

where $i/ROW/MIN_SALARY > 8000 

return $i

/

Notice that the "where" clause includes an XPath expression to indicate which elements of the XML document to test. In this case, the output as a whole has multiple ROW elements, each of which has a MIN_SALARY element. The XPath expression tests each such element against our cutoff value of 8000.

The "return" clause is returning the entire row. It could just as easily be an expression to indicate which parts of the row to return.

This example shows another feature of Oracle: the ora:view function. This XML function returns an XML representation of a relational table or view.

For more information about XML processing in Oracle, see the Oracle Database 10g XML DB Developer's Guide.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.