Two languages in DB2: SQL/XML and XQuery

DB29 introduced pureXML support, which means that XML data is stored and queried in its inherent hierarchical format. To query XML data, DB2 provides two languages: SQLXML and XQuery.

DB2 9 introduces pureXML support, which means that XML data is stored and queried in its inherent hierarchical format. To query XML data, DB2 provides two languages: SQL/XML and XQuery.

You can use XQuery and SQL separately, but you can also embed XQuery into SQL (or vice versa ). Each option is useful in a specific environment. This article will discuss these options, introduce their respective advantages and disadvantages, and provide the guiding principles for choosing an appropriate solution based on your needs.

The pureXML support in DB2 provides efficient and common functions for managing XML data. DB2 stores and processes XML data in its inherent hierarchical format, avoiding performance and flexibility constraints caused by storing XML as text in CLOB or ing it into a relational table. Different from using XML only, DB2 V9 also provides seamless integration of relational data and XML data, even in a row of a table. This flexibility is manifested in language support, allowing you to access relational data, XML data, or both types of data. You can query XML in any of the following four options:

• Common SQL (excluding XQuery)
• SQL/XML, that is, SQL statements embedded with XQuery
• XQuery as an independent language (excluding SQL)
• SQL-embedded XQuery

This document assumes that you are familiar with the basic concepts described in the two articles. Please note that XPath is a seed language of XQuery, so when we mention XQuery, it also secretly contains the XPath language. If you have used an XSLT style table or location path in DB2 XML Extender, You should have understood XPath. In many cases, XPath is enough to extract XML values or represent XML predicates. Therefore, you can start to use XPath even if you are not familiar with all other features of XQuery.

DB2 enables you to leverage all of these options to maximize productivity and adapt queries to application needs. The problems described in this article are as follows:

• What are the key features of these four options? What are their advantages and disadvantages?
• Under what circumstances should you choose?

Let's give a high-level summary first, and then study the details and specific instances of each option in detail.

Summary and guiding principles

You can express many queries in common XQuery, SQL/XML, or XQuery with built-in SQL. Under specific circumstances, you may find that one of them can express your query logic more intuitively than other solutions. Generally, the "correct" way to query XML needs to be selected based on "processing one by one", and the specific requirements and features of the application need to be considered. However, we can summarize the following guiding principles.

• Common SQL statements without XQuery or XPath are only useful for full-document search and the insert, delete, and update operations of the entire document. The document must be selected based on non-XML columns in the same table.
• SQL/XML statements with XQuery or XPath statements embedded in SQL provide the widest range of functionality and minimal limitations. You can represent predicates in XML columns, extract document fragments, pass parameter tags to XQuery expressions, use full text search, SQL-level aggregation, and grouping, you can also use a flexible method to associate and connect relational data with XML data. This solution can well serve the vast majority of applications. Even if you do not need to take advantage of all these advantages immediately, you may still consider choosing this solution so that your choice can cope with future expansion.
• XQuery is a powerful query language designed for querying XML data. Similarly, if your application only needs to query and manipulate XML data, and does not involve any relational data, XQuery is also an excellent choice. This solution may be simple and intuitive at times. In addition, if you are porting from a database that only uses XML to DB2 9 and there is an existing XQuery, you are likely to continue using XQuery.
• An SQL-embedded XQuery is a wise choice when you want to use relational predicates and indexes, and use full text search to pre-filter documents in XML columns that will then be input as XQuery. SQL embedded in XQuery allows you to run external functions on XML columns. However, if you want to perform data analysis queries with groups and aggregation, SQL/XML is a better choice.

No matter how you choose to combine SQL and XQuery in a statement, DB2 uses a hybrid compiler to generate and optimize an execution plan for the entire query-without causing performance loss for query execution.

The following table summarizes the advantages of the four different options for querying XML data.

Table 1. Summary

	Common SQL	SQL/XML	Common XQuery	SQL/XML-embedded XQuery
XML predicates	-	++	++	++
Relational predicates	++	++	-	+
XML and relational predicates	-	++	-	++
Connect XML with relational databases	-	++	-	++
Connect XML with XML	-	+	++	++
Convert XML data	-	O	++	++
Insert, update, and delete	++	++	-	-
Parameter mark	+	++	-	-
Full text search	+	++	-	++
XML aggregation and grouping	-	++	O	O
Function call	++	++	-	++

In the preceding table, "-" indicates that the given language does not support a certain feature. "+" indicates that this feature is supported, but it is more effective or convenient; "++" indicates that a given language is extremely suitable for this feature. Finally, "o" indicates that although this feature can be presented, the effect is very bad or inefficient to some extent.

Now, let's define some sample data and tables to view specific query examples.