Home:Learn XQuery:XQuery Tutorial:Querying XML Columns

Querying XML Columns with DataDirect XQuery®™

Increasingly, XML is 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 can now take advantage of relational databases that support a native XML data type to represent an XML document or XML document fragment. This paper shows how you can use DataDirect XQuery® to query XML stored as character data and query XML stored as XML.

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.

Sample Data

In the remainder of this article, we show queries that use each of the preceding approaches. These queries operate on the following table named HOLDINGSXML. This table contains a column named USERID and a column named XMLCOL.

Parsing XML Stored as Character Data

If your database does not support the XML type and you store XML documents as character data, you must parse the XML before it can be queried. This can be done with a Java external function called from within an XQuery query.

Returning an Entire XML Column

Using DataDirect XQuery®, it's straightforward to return an entire XML column because it does not require evaluation of the contents of the column.

Evaluating XML in Memory

DataDirect XQuery® supports an extension expression named evaluate in memory that forces an expression to be evaluated in memory using XQuery directly, not using SQL. This is a simple way to use XQuery functionality that is not available in SQL.

Using Database Vendor-Specific SQL Functions to Query XML

DataDirect XQuery® can call any SQL function as an external function. DB2 and Oracle provide SQL functions that allow you to return parts of an XML value stored in the database using path expressions similar to XPath 1.0.

Conclusion: Querying XML Columns with DataDirect XQuery®™

DataDirect XQuery® can query XML stored as character data, but you must provide a Java external function to parse the XML.

Appendix: Creating the Sample Data

We used the following methods to insert the XML data into the XML column named XMLCOL using the example data shipped with DataDirect XQuery®.