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.

Learn More

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.

Learn More

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.

Learn More

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.

Learn More

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.

Learn More

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.

Learn More

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.

Learn More

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®.

Learn More