Using SQL to query XML data in DB2 9

Although DB2 's hybrid architecture is quite different from previous versions, it is not difficult to take advantage of its new XML capabilities. If you are already familiar with SQL, you will soon be able to translate this skill into the processing of local XML data stored in DB2. You'll know how to do that with this article.

The XML features in DB2 Viper (that is, DB2 9) include new storage management, new indexing techniques, and support for query languages. In this article, learn how to use SQL or XML-extended SQL (sql/xml) to query DB2 XML columns for data. The next article will discuss the new support for the emerging industry standard XQuery in DB2, and explore when XQuery is most useful.

You might be surprised that DB2 also supports bilingual queries-that is, queries that combine expressions from SQL and XQuery. What language (or two languages) should be used depends on the needs of the application, but also on the skills you possess. In fact, combining elements from both query languages into one query is not as difficult as you might think. This can also provide powerful capabilities for searching and integrating traditional SQL and XML data.

Sample Database

The query in this article accesses the sample database that was created in the "DB2 Viper QuickStart" (developerworks,2006 year April). Here we briefly review the definition of "items" and "clients" tables in the sample database:

Listing 1. Definition of a table

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 in the "items.comments" column, and figure 2 shows the sample XML data in the "Clients.contactinfo" column. Subsequent query examples will refer to one of the XML documents or to certain elements in the two documents.

Figure 1. Sample XML document stored in the Comments column of the items table



Figure 2. Sample XML document stored in the "Clients" table "contactinfo" column

Query environment

All the queries in this article are sent interactively, and you can issue queries through the DB2 command line processor or the DB2 command Editor in DB2 control Center. The screen images and instructions in this article are primarily based on the latter. (DB2 Viper also comes with an Eclipse based Developer Workbench that can help programmers graphically construct queries.) However, this article does not discuss application development issues or Developer Workbench. ）

To use the DB2 Command Editor, you need to start control Center and select Tools > Command Editor. The window shown in Figure 3 will pop up. Enter the query in the upper panel, click the green arrow in the upper-left corner to run the query, and then view the output in the panel below or the Query Results tab.

Figure 3. DB2 Command Editor, which can be started from DB2 control Center

Pure SQL Query

Even if you have limited knowledge of SQL, you can still easily query XML data. For example, the following query selects all of the contents of the "clients" table, including the XML information stored in the "ContactInfo" column:

Listing 2. A simple SELECT statement

SELECT * FROM Clients

You can, of course, write more selective SQL queries that include relational projections and restriction operations. The following query retrieves the ID, name, and contact information for all customers with a "Gold" status. Note that the "ContactInfo" column contains XML data, while the other two columns do not contain XML data:

Listing 3. Simple SELECT statement with projections and restrictions

Select ID, name, contactinfo

From clients

where status = ' Gold '

As you might expect, you can create a view based on such a query, as the following "Goldview" illustrates:

Listing 4. Create a view that contains XML columns

CREATE View Goldview as

Select ID, name, contactinfo

From clients

where status = ' Gold '

Unfortunately, a lot of things can't be solved by using SQL. You can retrieve an entire XML document through a pure SQL statement (as you have just shown), but you cannot specify an xml-based query predicate, nor can you retrieve a portion of an XML document or a specific element value in an XML document. In other words, using pure SQL does not project, restrict, connect, aggregate, or sort operations on fragments in an XML document. For example, you cannot retrieve the e-mail address of a Gold customer alone or the name of a customer residing in a region with a postal code of "95116". To express these types of queries, you need to use both query languages with XML-extended SQL (Sql/xml), XQuery, or combination.