Using an XQuery query to DB2 XML data _db2

The DB2 tutorial you're looking at is 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:

• For: Iterate through the input sequence, then bind a variable to each entry
• Let: Declare a variable and assign it a value, possibly a list that contains multiple items
• Where: Specify criteria for filtering query results
• ORDER BY: Specify the sort orders for the results
• Return: Defines the results returned

The path expression in XQuery consists of a series of steps (step), separated by a slash. In the simplest form, each step navigates down the XML hierarchy to discover the children returned by the previous step. Each step in a path expression can also contain a predicate that filters the elements returned by that step and retains only those elements that satisfy a certain condition. For example, if a variable $clients is bound to a list of XML documents that contain <Client> elements, the 4-step path expression $clients/client/address[state = "CA"]/zip will return the customer residing in California 's postal code.

In many cases, you can write a query using either a FLWOR expression or a path expression.

Using DB2 XQuery as the top-level query language

To perform XQuery directly in DB2 Viper (rather than embedding it in an SQL statement), you must start with the keyword XQuery as the query. This keyword will instruct DB2 to invoke its XQuery parser to process the request. Note that this is only necessary when using XQuery as the outermost (top-level) language. If you embed an XQuery expression in SQL, you do not need to precede the statement with an XQuery keyword. However, this article uses XQuery as the base language, so all queries are preceded by XQuery.

When XQuery is used as a top-level language, it requires a source of input data. One way XQuery gets the input data is by calling a function called Db2-fn:xmlcolumn, with a parameter that indicates the table name of the XML column in the DB2 table and the column name of the column. The Db2-fn:xmlcolumn function returns a series of XML documents stored in a given column. For example, the following query returns a series of XML documents that contain customer contact information:

Listing 2. Returns a simple XQuery of customer contact data



X

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