Using an XQuery query to DB2 XML data

About XQuery

XQuery differs in many key ways from SQL, largely because the two languages are designed for two data models with different characteristics. XML documents contain hierarchies and have their intrinsic order. The tabular data structures supported by the SQL-BASED database management system are planar (flat) and are based on collections; therefore, there is no order between rows.

The difference between the two data models results in a number of basic differences in their respective query languages. For example, XQuery supports path expressions to allow programmers to navigate in the XML hierarchy, while pure SQL (without XML extensions) is not supported. XQuery supports both types and untyped data, and SQL data is always defined with the specified type. XQuery does not have a null value because the XML document ignores missing or unknown data. Of course, SQL uses NULL to represent missing or unknown data values. XQuery returns a series of XML data, while SQL returns the result set of various SQL data types.

This is only part of the fundamental difference between XQuery and SQL. Providing a detailed list is beyond the scope of this article, but the upcoming IBM Systems Journal will discuss the different languages in more detail. Let's explore some basic aspects of the XQuery language and see how it can be used to query the XML data in DB2 Viper.

Sample Database

The query in this article accesses the sample tables that were created in the "DB2 Viper QuickStart" (developerworks,2006 year April). Listing 1 shows the definition 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

The sample XML data contained in the "items.comments" column is shown in Figure 1, and the sample XML data contained in the "Clients.contactinfo" column is shown in Figure 2. The following example query will refer to a specific element in one or two of these 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

Query environment

All of the queries in this article are sent in an interactive manner. This can be done through the DB2 command line processor or DB2 control Center's DB2 command Editor. The screen images and instructions in this article are primarily used in the latter way. (DB2 Viper also comes with a Developer Workbench based on Eclipse that can help programmers construct queries graphically.) This article does not discuss application development issues and Developer Workbench. ）

To use the DB2 command Editor, start control Center and select the Tools-> command Editor. The window shown in Figure 3 appears. 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 either in the panel below or in the Query Results tab.

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

XQuery Example

As in "DB2 XML data with SQL queries," This article will step through some common business scenarios and show how to use XQuery to satisfy requests for XML data. This article also explores more complex scenarios in which you need to embed SQL in XQuery.

XQuery provides several different types of expressions that can be combined randomly. Each expression returns a series of values that can be used as input to other expressions. The result of the outermost expression is the result of the query.

This article focuses on two important XQuery expressions: "FLWOR" expressions and path expressions. The FLWOR expression is very much like a select-from-where expression in SQL--it is used to iterate over a list of multiple items, and optionally returns the value that is computed on each item. A path expression can navigate between layered XML elements and return elements found at the end of the path.

Like an select-from-where expression in SQL, an XQuery FLWOR expression can contain several clauses that begin with a keyword. In a FLWOR expression, the following keywords are used to begin as a clause: