Query DB2 XML data using XQuery

The DB2 tutorial is: Query DB2 XML data using XQuery.

About XQuery
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 allowProgramUsers navigate in the XML hierarchy, while pure SQL (without XML extension) does not. 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

Query Environment

All queries in this article are sent through interaction. This can be done through the DB2 command line processor or the DB2 command editor of the DB2 control center. The screen images and descriptions in this article mainly use the latter method. (DB2 Viper also comes with an eclipse-based developer workbench that helps programmers construct queries in a graphical manner. This article does not discuss application development issues and developer workbench .)

To use DB2 command Editor, Start Control Center, and select Tools> command editor. The window shown in Figure 3 appears. In the preceding panel, enter a query and click the green arrow in the upper left corner to run the query. Then, you can view the output in the following panel or on the query results tab.

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

XQuery example

As in "querying DB2 XML data with SQL", this article will gradually explain some common business scenarios and show how to use XQuery to meet XML data requests. This article also explores more complex scenarios where SQL needs to be embedded in XQuery.

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

This article mainly discusses two important XQuery expressions: the "flwor" expression and the path expression. The flwor expression is very similar to the select-from-where expression in SQL -- it is used to iterate a list composed of multiple items, and can return the value calculated by each item. The path expression allows you to navigate between Hierarchical XML elements and return the elements found at the end of the path.

Similar to the select-from-where expression in SQL, The XQuery flwor expression can contain several clauses starting with a keyword. In a flwor expression, the following keywords are used to start a clause:

• For: iterates the input sequence and binds a variable to each input item in sequence.
• Let: declare and assign values to a variable. It may be a list containing multiple items.
• Where: specifies the criteria for filtering query results
• Order by: Specifies the sorting order of the results.
• Return: defines the returned results.

The path expressions in XQuery are composed of a series of steps separated by a slash. In the simplest form, each step is directed down the XML hierarchy to find the child of the element returned by the previous step. Each step in a path expression can also contain a predicate used to filter the elements returned by this step and retain only the elements that meet certain conditions. For example, assume that the variable $ clients is bound to a list of XML documents containing the <client> element, then the four-step path expression $ clients/client/address [State = "ca"]/zip will return the zip code of the customer who lives in California.

In many cases, you can use the flwor expression or path expression to compile a query.

Use DB2 XQuery as the top-level Query Language

To directly execute XQuery in DB2 viper (instead of embedding it in SQL statements), it must begin with the keyword XQuery. This keyword instructs DB2 to call its XQuery parser to process requests. Note that this is only required when XQuery is used as the outermost (top-layer) language. If you embed an XQuery expression in SQL, you do not need to add the XQuery keyword before the statement. However, this article uses XQuery as the basic language, so all queries are prefixed with XQuery.

When XQuery is used as a top-level language, it requires an input data source. One way for XQuery to obtain input data is to call a function named db2-fn: xmlcolumn with a parameter indicating the name of the table where the XML column in the DB2 table is located and the name of the column. Db2-fn: The xmlcolumn function returns a series of XML documents stored in a given column. For example, the following query returns a series of XML documents containing the customer contact information:

List 2. simple XQuery that returns customer contact information data

X

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