Introduction: Querying XML Columns with DataDirect XQuery®™

Increasingly, XML documents or XML document fragments are stored in relational databases as structured XML. Some applications will continue to shred XML into a relational format or store XML as CLOB values. Other applications now can take advantage of databases that support a native XML data type to represent an XML document or XML document fragment, such as Oracle 10g, Microsoft SQL Server 2005, and DB2 UDB 8.x (through the XML Extender).

Querying XML Stored As Character Data

When XML is stored as character data, it must be parsed before it can be queried as XML. You can parse XML stored as character data using Java external functions.

Querying XML Stored as XML

The SQL standard is evolving to allow XQuery to be used directly in a SQL query, providing a convenient way to query XML, but many relational databases that are currently shipping do not support this ability or implement it with limited functionality. Because DataDirect XQuery® uses SQL to query relational databases, this means that DataDirect XQuery® cannot directly evaluate XQuery expressions against XML stored in a column of a relational database.

Fortunately, you can always return an entire column that contains XML, and workarounds exist that allow you to write almost any query you need. Unfortunately, these workarounds force you to choose between database independence and performance.

Future versions of DataDirect XQuery® will solve this problem. In the meantime, you can execute XQuery queries against XML columns using one of the following three approaches:

  1. Return an entire XML column.

  2. Evaluate part of the query in memory. Although DataDirect XQuery® cannot evaluate path expressions against XML in the database, you can force DataDirect XQuery® to evaluate an expression against an XML column in memory. This approach is portable and allows the full power of XQuery, but it often is slower than the third approach because it does not leverage indexes and consumes more memory.

  3. Use database vendor-specific SQL functions in an XQuery query to return XML stored in the database. This approach is not portable and is limited to the functions provided by a given vendor. It is generally faster than the second approach because the vendor usually takes advantage of indexes that support these functions and because it does not require the entire XML column to be instantiated in memory.