Query DB2 XML data using XQuery (1)

You may have heard of the new DB2 Viper architecture, which supports table and hierarchical data structures. In fact, the previous article summarized the New XML features in DB2 and described how to create database objects and populate these database objects with XML data, it explains how to use SQL and SQL/XML to operate XML data. This article continues to explore the DB2 XML feature. This time, we will focus on DB2's new support for XQuery.

DB2 uses XQuery as the best language, allowing users to directly write an XQuery expression, rather than encapsulating or embedding XQueries into SQL statements. Furthermore, DB2's query engine processes XQueries locally, meaning that it does not have to translate them into SQL behind the scenes before parsing, computing, and optimizing XQueries. Of course, if you choose to write a "bilingual)" query that includes both the XQuery and SQL expressions, DB2 will also process and optimize these queries.

Like the description of SQL/XML in "querying DB2 XML data with SQL", this article also mentions some common query tasks and shows you how to use XQuery to accomplish these objectives. But first, let's take a brief look at the differences between XQuery and SQL.

AboutXQuery

XQuery differs from SQL in many key aspects, largely because these two languages are designed for two data models with different characteristics. XML documents contain hierarchies and have an inherent order. The SQL-based database management system supports flat and set-based table data structures. Therefore, there is no order between rows.

The two data models have different query languages. For example, XQuery supports path expressions to allow programmers to navigate the XML hierarchy, while pure SQL does not support XML extension. XQuery supports data with and without types, while SQL data is always defined by a specified type. There is no null value for XQuery, Because XML documents ignore missing or unknown data. Of course, SQL uses null to indicate missing or unknown data values. XQuery returns a series of XML data, while SQL returns results of various SQL data types.

This is only part of the basic difference between XQuery and SQL. A detailed list is provided beyond the scope of this article, but the upcoming IBM Systems Journal will discuss the differences between these languages in more detail. Now let's explore some basic aspects of the XQuery language and see how to use it to query XML data in DB2 Viper.

Sample Database

Query the sample table created in "DB2 Viper Quick Start" developerWorks, November April 2006. Listing 1 defines the "items" and "clients" tables in the sample database:

Listing 1. Table Definition

create table items (id int primary key not null, brandname varchar(30), itemname varchar(30), sku int, srp decimal(7,2), comments xml)create table clients(id int primary key not null, name varchar(50), status varchar(10), contactinfo xml)

Figure 1 shows the sample XML data contained in the "items. comments" column, while Figure 2 shows the sample XML data contained in the "clients. contactinfo" column. The subsequent example query references a specific element in one or two of the Two XML documents.

Figure 1. sample XML document stored in the "comments" column of the "items" table

Figure 2. sample XML document stored in the "contactinfo" column of the "clients" table