Introduction

This article provides a quick reference for querying XML data with DB2 pureXML. This article reviews the syntax associated with some common queries, including the XQuery with SQL format and the SQL/XML with XQuery format. And even once you select between those two formats, there are often multiple ways in which you can write most queries to achieve the same results.

This article is primarily targeted at software architects, designers, and developers that have familiarity with XML. This article assumes you are using IBM development tools such as Rational® Software Architect, Rational Application Developer, Optim® Development Studio, or InfoSphere™ Data Architect to work with XML data within DB2.

Setting up the environment

Begin by setting up your environment to work with the queries.

Accessing the templates

The syntax used for each query example in this article is derived from the content found within the editor templates provided with the IBM tools. To access the set of templates within the tools, complete the following steps:

1. Navigate to Window > Preferences.
2. Within Preferences, navigate to Data Management > SQL Development > SQL and XQuery Editor > Templates, as shown in Figure 1.
3. Review the available templates, import templates provided by others, or update the templates and export them to share with others.

When you edit SQL or XQuery scripts, you can access and use the available templates by using the CTRL+Space key-combination within the editor window.

Setting up the database tables

The examples in this article use the SAMPLE database tables with XML data that DB2 provides. You can install these examples as part of DB2 First Steps, as shown in Figure 2, or by using db2sampl -xml from the command line after installation.

The example queries in this article use the CUSTOMER table from the SAMPLE database, as shown in Figure 3.

Listing 1 shows a sample XML document as found in the INFO column of the CUSTOMER table.

<customerinfo Cid="1001">
    <name>Kathy Smith</name>
    <addr country="Canada">
        <street>25 EastCreek</street>
        <city>Markham</city>
        <prov-state>Ontario</prov-state>
        <pcode-zip>N9C 3T6</pcode-zip>
    </addr>
    <phone type="work">905-555-7258</phone>
</customerinfo>

Back to top

Creating queries using XQuery

This section describes how to create queries using XQuery.

Types of XQuery expressions

The following list shows some of the different types of XQuery expressions that can be used within a query.

Primary

Use of basic primitives of the language, including literals, variable references, parenthesized expressions, function calls, and so on

Path

Identify nodes within an XML tree using syntax of XPath 2.0

FLWOR

Iterate over sequences, and bind variables to intermediate results.

Comparison

Compare two values

Constructors

Create XML structures within a query

Logical

Use and/or to compute Boolean values.

Conditional

Use if, then, and else to evaluate whether the value of a test expression is true or false.

Basic queries

There are two basic approaches to querying XML data in DB2 with XQuery. You can use the DB2 sqlquery function to use SQL to access specific XML data, or you can use the DB2 xmlcolumn function to use XQuery to access all XML data in a column. Listing 2 shows the basic syntax.

xquery db2-fn:sqlquery(${sql_query})${xquery_expression}

Listing 3 shows a simple query whereby you use SQL to return a subset of the documents in the INFO column. At the end of the query, include an XPath expression to retrieve the name element from within the XML document.

xquery db2-fn:sqlquery("select INFO from CUSTOMER where CID = 1000")/customerinfo/name

In this case, you are interested in all of the XML documents in the INFO column. Then for each document, you use a Path expression to return the name elements.

Listing 4 shows the xmlcolumn syntax.

xquery db2-fn:xmlcolumn('${schema}.${table}.${column}')${xquery_expression}

Listing 5 gives all of the XML documents in the INFO column. Then for each document, you use a Path expression to return the name elements.

xquery db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo/name

FLWOR

A common approach in using XQuery is to write queries that include the following clauses, some of which are optional: For, Let, Where, Order by, and Return. These are abbreviated as FLWOR (and pronounced flower). Keep in mind that XQuery expects XML input, and it returns XML. The syntax is shown in Listing 6.

xquery
for $$i in ${xquery_expression}
let $$x := ${xquery_expression}
where ${xquery_expression} 
order by ${xquery_expression} 
return ${xquery_expression}

The syntax in Listing 6 includes all of the available FLWOR expression elements. Following are examples that show that you can modify the structure of the query with the optional elements.

Listing 7 is an example of a simple FLWOR query to retrieve the city element associated with each address for each customerinfo element.

xquery
for $c in db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo 
return $c/addr/city
 

Listing 8 adds to the previous query, adding a restriction to bring back the city for only the customer with attribute Cid = 1001.

xquery
for $c in db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo 
where $c/@Cid = 1001 
return $c/addr/city

Listing 9 extracts the names for the customers for whom prov-state is Ontario, and it alphabetizes the results by the name.

xquery
for $c in db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo 
where $c/addr/prov-state = "Ontario"
order by $c/@name  
return $c/name

Listing 10 uses Let to assign a variable to hold the pcode-zip value for customers that live in Canada and then alphabetizes the results by Cid. The code then constructs a new XML element, <contact>, which contains the Cid and pcode-zip.

xquery
for $c in db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo 
let $pc := $c/addr/pcode-zip
where $c/addr/@country = "Canada"
order by $c/@Cid  
return <contact>{$c/@Cid}{$pc}</contact>

Those query examples were quite simple, and they do not account for namespaces. To support namespaces, you can add the namespace to the elements listed in the query; or you can declare a default namespace using the command declare default element namespace '${namespace_uri}';.

Listing 11 shows an XML document that uses a namespace declaration.

<customerinfo xmlns="http://poindustry.org" Cid="1001">
    <name>Kathy Smith</name>
    <addr country="Canada">
        <street>25 EastCreek</street>
        <city>Markham</city>
        <prov-state>Ontario</prov-state>
        <pcode-zip>N9C 3T6</pcode-zip>
    </addr>
    <phone type="work">905-555-7258</phone>
</customerinfo>

Because you are dealing with a simple case of just a single namespace declaration, you can take either of two approaches in querying this document.

Listing 12 declares a default namespace. You do not have to modify element references in the query to include the namespace, because by default, they access this single namespace.

xquery
declare default element namespace = "http://poindustry.org";
for $c in db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo 
return $c/addr/city

Listing 13 leverages multiple namespaces by defining a namespace prefix, such as cust-ns, to be used as a shorthand reference to the namespace. Once defined, use this namespace prefix along with any element names that you reference.

xquery
declare namespace cust-ns = "http://poindustry.org";
for $c in db2-fn:xmlcolumn("CUSTOMER.INFO")/cust-ns:customerinfo 
return $c/cust-ns:addr/cust-ns:city

For the namespace examples in Listing 12 and Listing 13, remember that the semicolon is a typical statement terminator for queries. When working with namespaces, change the statement terminator to a different character, such as the hash symbol (#).

Transform and updates

This section describes queries for updating specific content (elements or attributes) within an XML document. The queries discussed leverage the XQuery Update Facility, which is an update to the language and semantics of XQuery 1.0. You can use this type of query to update, replace, delete, or insert content into an XML document. This change can be made to the data in the database or applied to the data returned from a query. The query syntax changes slightly as you perform different actions. Listing 14 shows an example of the basic syntax for a replace query.

UPDATE ${table}
SET ${xml_col} = XMLQUERY('
transform 
copy $$x := ${xquery_expression}
modify do replace value of ${xquery_expression}
with ${xquery_expression}
return ${xquery_expression}')
WHERE ${expression}

The following are examples that modify the syntax to show support for the other Transform actions.

Listing 15 updates the value of the city element for a specific customer. Note that transform is an optional part of the query. As such, you can omit it for the next few examples.

UPDATE customer
SET info = XMLQUERY('
transform 
copy $c := $INFO
modify do replace value of $c/customerinfo/addr/city
with "Toronto"
return $c')
WHERE CID =1001

Listing 16 deletes the city element for a specific customer.

UPDATE customer
SET info = XMLQUERY('
copy $c := $INFO
modify do delete $c/customerinfo/addr/city
return $c')
WHERE CID =1001

Inserts are interesting, as you need to give thought on where in the document you want to insert information. Listing 17 is non-specific about where in the document to insert the new element. The actual place of insertion is non-deterministic.

UPDATE customer
SET info = XMLQUERY('
copy $c := $INFO
modify do insert <city>Toronto</city> 
into $c/customerinfo/addr
return $c')
WHERE CID =1001

If you need a specific placement, you can look at one of a number of alternate approaches to inserting data. Listing 18 inserts data after the phone element.

UPDATE customer
SET info = XMLQUERY('
copy $c := $INFO
modify do insert <phone type="cell">905-555-7272</phone> 
after $c/customerinfo/phone
return $c')
WHERE CID =1001

Listing 19 inserts an element as the first subelement within addr.

UPDATE customer
SET info = XMLQUERY('
copy $c := $INFO
modify do insert <company-name>ACME Co</company-name> 
as first into $c/customerinfo/addr
return $c')
WHERE CID =1001

Listing 20 inserts an element as the last subelement within addr.

UPDATE customer
SET info = XMLQUERY('
copy $c := $INFO
modify do insert <company-name>ACME Co</company-name> 
as last into $c/customerinfo/addr
return $c')
WHERE CID =1001

Listing 21 inserts an element into the document before the addr element.

UPDATE customer
SET info = XMLQUERY('
copy $c := $INFO
modify do insert <email>ksmith@acme.com</email> 
before $c/customerinfo/addr
return $c')
WHERE CID =1001

Listing 22 renames an existing element by changing the addr element to become address.

UPDATE customer
SET info = XMLQUERY('
copy $c := $INFO
modify do rename $c/customerinfo/addr 
as "address" 
return $c')
WHERE CID =1001

Listing 23 shows a transform query that only modifies the result of the query and does not alter the contents of the database.

xquery
copy $c := db2-fn:sqlquery('select INFO from CUSTOMER where CID =1001')
modify do rename $c/customerinfo/addr 
as "address" 
return $c

Back to top

Creating queries using SQL/XML

This section offers examples of creating queries using SQL/XML.

Basic SQL/XML query

A good place to start is to look at the barebones syntax for a SQL/XML query, as shown in Listing 24.

SELECT XMLQUERY('${xquery_expression}') 
FROM ${table_name} 
WHERE XMLExists('${xquery_expression}')

There are a couple of approaches to using this syntax with different types of XQuery expressions. First look at an example that incorporates a path expression

Listing 25 shows a query to get the name of the customers who live in the city of Markham. The XMLExists predicate is used to determine whether an XQuery expression returns a sequence of one or more items. Note that you need to use square brackets ([ ]) to surround any value predicates within the XQuery expression. Doing so ensures that the evaluation of the expression is in accordance with what is semantically expected. If you omit the square brackets, the result of the XQuery expression will always return a sequence, and in turn XMLExists will always be true.

SELECT XMLQUERY('$INFO/customerinfo/name')
FROM CUSTOMER
WHERE XMLEXISTS('$INFO/customerinfo/addr[city= "Markham"]')

Next, look at one more basic example that uses a FLWOR expression along with XMLQUERY. Listing 26 returns the addr element for the customer with an attribute Cid > 1002 and where the country = "Canada".

SELECT XMLQUERY('for $i in $INFO/customerinfo/addr 
			return $i')
FROM CUSTOMER
WHERE XMLEXISTS('let $i := $INFO/customerinfo
			where $i/@Cid > 1002
			and $i/addr/@country = "Canada"
			return $i')

XMLTable

There are times when you need to return a relational table based on information from an XML document. You can use XMLTable to create such a result.

At its most basic, the XMLTable syntax is shown in Listing 27.

SELECT X.${new_col1_name}, X.${new_col2_name}, ${table}.${col}
FROM XMLTable(${row_generating_xquery_expression}
COLUMNS  
${new_col1_name}  ${new_col1_data_type} PATH '${column_generating_xquery_expression}',
${new_col2_name}  ${new_col2_data_type} PATH '${column_generating_xquery_expression}'
) AS X

Listing 28 offers a simple example of using XMLTable. You are returning rows based on two values from an XML document. Each of the two elements returned from the XML document appears in its own column. Use X.* to indicate that you want to return all columns that XMLTable provides.

SELECT X.*
FROM customer,
XMLTABLE('$INFO/customerinfo'
COLUMNS
	custname	VARCHAR(20)	PATH 'name',
	city		VARCHAR(20)	PATH 'addr/city') AS X

In addition to returning columns based on information from the XML document, you can also return columns from relational data, as shown in Listing 29.

SELECT customer.CID, X.*
FROM customer,
XMLTABLE('$INFO/customerinfo'
COLUMNS
	custname	VARCHAR(20)	PATH 'name',
	city		VARCHAR(20)	PATH 'addr/city') AS X

Listing 30 adds a new column that is of type XML where you are constructing a new XML document on the fly.

SELECT customer.CID, X.*
FROM customer,
XMLTABLE('$INFO/customerinfo'
COLUMNS
	custname	VARCHAR(20)	PATH 'name',
	city		VARCHAR(20)	PATH 'addr/city',
	newXML		XML		PATH '<newXML>{addr/pcode-zip}</newXML>') AS X

Back to top

Conclusion

This article provided a quick overview of query options when working with XML data and DB2 pureXML. The goal is to provide a short and accessible reference to be used as you ramp up with DB2 pureXML. As with learning any programming language, the best way to grow skills is to write queries. In addition to working with the SAMPLE database, see Resources for other industry schemas.

Resources

Learn

• Consult the DB2 pureXML enablement wiki for access to articles, papers, presentations, and demonstrations related to the use of DB2 and pureXML.
  
  
• Check out the XML area on developerWorks to get the resources you need to advance your XML skills, including DTDs, schemas, and XSLT.
  
  
• Refer to "Get started with Industry Formats and Services with pureXML: A fastpath to storing your industry XML content in DB2," (developerWorks, May 2007) which provides an overview of the industry bundles that have been published through IBM alphaWorks.
  
  
• Get cooking with DB2 pureXML Cookbook: Master the Power of the IBM Hybrid Data Server book, which provides comprehensive coverage on using DB2 and pureXML. In addition to providing in-depth explanations, many examples are provided.
  
  
• Refer to these related standards:
  • XQuery 1.0
  • XPath 2.0
  • XQuery Update Facility
  
  
• See the book XQuery by Priscilla Walmsley for an additional resource for XQuery.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Explore alphaWorks: Industry Formats and Services with pureXML for access to the available industry bundles.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Join the pureXML Devotees community, which focuses on the use of DB2 and pureXML through webcasts covering a variety of features, best practices, and tooling options.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Lee Ackerman has been with IBM for over 10 years, and he is a Master Certified IT Specialist. Currently, he is a pureXML Solution Architect with the Information Management Technology Ecosystem team. Lee focuses on helping customers, partners, and IBMers succeed with DB2 and pureXML.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Setting up the environment
• Creating queries using XQuery
• Creating queries using SQL/XML
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers