Use SQL to query XML data in DB2 9

The db2 tutorial is: use SQL to query XML data in DB2 9.

Although the hybrid architecture of DB2 is quite different from that of earlier versions, it is not difficult to use its new XML features. If you are familiar with SQL, you can quickly convert this skill to processing local XML data stored in DB2. This article will show you how to achieve this.

XML features in DB2 Viper (DB2 9) include new storage management, new indexing technology, and support for query languages. This article describes how to use SQL or SQL (SQL/XML) with XML extensions to query data in the DB2 XML column. The next article will discuss the newly introduced support for emerging industry-standard XQuery in DB2 and explore when it is most useful.

You may be surprised that DB2 also supports bilingual queries-queries that combine expressions from SQL and XQuery. The language to use (or the combination of the two languages) depends on the needs of the application and the skills you have mastered. In fact, it is not as difficult as you think to combine the elements in the two query languages into a query. This can also provide powerful capabilities for searching and integrating traditional SQL and XML data.

Sample Database

The query in this article will access the sample database created in "DB2 Viper Quick Start" (developerWorks, November April 2006. Here we will briefly review the definitions of 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 in the "items. comments" column, and Figure 2 shows the sample XML data in the "clients. contactinfo" column. The following query example references an XML document or some specific elements in the two documents.

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



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

Query Environment

All queries in this article are sent interactively. You can use the DB2 Command line processor or the DB2 Command Editor in the DB2 Control Center to issue queries. The screen images and descriptions in this article are mainly based on the latter method. (DB2 Viper also comes with an Eclipse-based Developer Workbench that helps programmers construct queries graphically. However, this article does not discuss application development issues or Developer Workbench .)

To use DB2 Command Editor, you need to start Control Center and select Tools> Command Editor. The window shown in Figure 3 is displayed. Enter Query in the preceding panel, click the green arrow in the upper left corner to run the Query, and view the output in the following panel or the Query results tab.

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

Pure SQL query

Even if you know little about SQL, you can easily query XML data. For example, the following query Selects all the content in the "clients" table, including the XML Information stored in the "contactinfo" column:

Listing 2. Simple SELECT statement

select * from clients

Of course, you can also write more selective SQL queries to include relational projection and restricted operations. The following query retrieves the IDs, names, and contact information of all customers in the "Gold" status. Note that the "contactinfo" column contains XML data, while the other two columns do not include XML data:

Listing 3. Simple SELECT statements with projection and restrictions

select id, name, contactinfo 

from clients

where status = 'Gold'

As you expected, you can create a view based on this query. The following "goldview" describes this:

Listing 4. Creating a view containing XML Columns

create view goldview as 

select id, name, contactinfo 

from clients

where status = 'Gold'

Unfortunately, many things cannot be solved simply by using SQL. You can use a pure SQL statement to retrieve the entire XML document (this has been proved just now), but you cannot specify an XML-based query predicate, you cannot retrieve a part of an XML document or a specific element value in an XML document. In other words, you cannot use SQL statements to project, restrict, join, aggregate, or sort fragments in an XML document. For example, you cannot retrieve the email address of a Gold customer or the name of a customer living in a zip code "95116. To express these types of queries, you need to use SQL (SQL/XML) with XML extensions, XQuery, or both query languages.

The next section describes the basic features of SQL/XML. In the next article, we will learn how to write an XQuery and how to combine it with SQL.

SQL/XML query

As the name suggests, SQL/XML is designed to build a bridge between SQL and XML. It is first part of the SQL standard and has evolved to include the specification for embedding an XQuery or XPath expression into an SQL statement. XPath is a language used to navigate XML documents to discover elements or attributes. XQuery includes support for XPath.

Make sure that the XQuery (and XPath) expressions are case sensitive. For example, the XQuery that references the XML element "zip" does not apply to XML elements named "ZIP" or "Zip. Sometimes it is hard for SQL programmers to remember the case sensitivity, because the SQL query syntax allows the use of "zip", "ZIP", and "Zip" to reference the same column name.

DB2 Viper provides over 15 SQL/XML functions to search for specific data in the XML document, convert traditional data to XML, and convert XML data to relational data, and other useful tasks. This article does not discuss all aspects of SQL/XML, but just talks about several common query challenges.

[1] [2] [3] [4] [5] [6] Next