Xquery in DB2

[ssahil11]

I have recently started working with DB2 database. I have heard about Xquery. What exactly Xquery is?

[dilasing]

XQuery is a new query language designed specifically to work with XML data. Xquery is a part of the W3C industry standard. It lets users navigate through the hierarchical structure inherent in XML documents. As a result, you can retrieve XML documents or document fragments using XQuery. You can also write XQuery that include XML-based predicates to "filter out" unwanted data from the results that DB2 will return.

[ssahil11]

I have heard that XQuery is similar to SQL. Is it true?

[dilasing]

Many SQL users mistakenly assume that XQuery is very similar to SQL. However,
XQuery differs from SQL in many ways largely because the languages were designed to work with different data models that have different characteristics. XML documents contain hierarchies and possess an inherent order. By contrast, tables supported by relational DBMSs (or, more precisely, SQL-based DBMSs) are flat and set-based, so rows are unordered.

[dilasing]

Also, XQuery lacks null values because XML documents omit missing or unknown data. SQL uses nulls to represent missing or unknown data values. XQuery returns sequences of XML data; SQL returns result sets of various SQL data types.
XQuery operates only on XML data. SQL operates on columns defined on traditional
SQL types and SQL/XML (SQL with XML extensions) operates on both XML data and traditional types of SQL data.

[ssahil11]

What is Path Expression in Xquery?

[dilasing]

XQuery supports XPath expressions that allow users to navigate through an XML document hierarchy to locate portions of interest. XPath expressions look very much like the expressions you use when working with a traditional computer file system. A path expression in XQuery consists of a series of "steps" separated by slash characters. In its simplest form, each step navigates downward in an XML hierarchy to find the children of the elements returned by the previous step. Each step in a path expression may also contain a predicate that filters the elements that are returned by that step, retaining only elements that satisfy some condition. You can check http://www.ibm.com/developerworks/db2/library/techarticle/dm-0604saracco/ to learn basic XQuery expressions for querying DB2 XML data XQuery.

[ssahil11]

And what is FLWOR Expression?

[dilasing]

FLWOR Expression is much like a SELECT-FROM-WHERE expression in SQL. It is used to iterate through a sequence of items and to optionally return something that is computed from each item. XQuery FLWOR expression may contain multiple clauses denoted by keywords. The clauses of a FLWOR expression begin with the following keywords:
• for: Iterates through an input sequence, binding a variable to each input item in turn
• let: Declares a variable and assigns it a value, which may be a list containing multiple items
• where: Specifies criteria for filtering query results
• order by: Specifies the sort order of the result
• return: Defines the result to be returned

[s.piyush58]

Can someone tell more about DB2 support for Xquery?

[dilasing]

DB2 treats XQuery as a first-class language, allowing users to write Xquery expressions directly rather than requiring that users embed or wrap XQueries in SQL statements. DB2's query engine processes XQueries natively, meaning that it parses, evaluates, and optimizes XQueries without ever translating them into SQL behind the scenes. You can check http://www.ibm.com/developerworks/db2/library/techarticle/dm-0603saracco2/ to know how to use several popular SQL/XML expressions to query DB2 XML data XQuery.

[s.piyush58]

Can I use Xquery and SQL both in single query in DB2?

[dilasing]

Yes, you can write "bilingual" queries that include both XQuery and SQL expressions. DB2 will process and optimize these queries as well. To embed SQL in an XQuery, you use the db2-fn:sqlquery function in place of the db2-fn:xmlcolumn function. The db2 fn:sqlquery function executes an SQL query and returns only the selected data. The SQL query passed to db2-fn:sqlquery must only return XML data; this enables XQuery to further process the result of the SQL query. Please check http://www.ibm.com/developerworks/db2/library/techarticle/dm-0606nicola/ to know when to use SQL/XML or Xquery to query the XML data.

[s.piyush58]

How to write and execute XQueries in DB2?

[dilasing]

To execute an XQuery directly in DB2, you must preface the query with the keyword XQuery. This instructs DB2 to invoke its XQuery parser to process your request. You only need to do this if you are using XQuery as the outermost (or top level) language. If you embed XQuery expressions in SQL, you don't need to preface them with the XQuery keyword.

[s.piyush58]

Can I embed XQueries in SQL?

[ssahil11]

How to validate XML data with a XML schema while inserting XML data?

[dilasing]

How to validate XML data with a XML schema while inserting XML data?

This is answered in the XML Schema thread. Please refer to that thread.

[dilasing]

Can I embed XQueries in SQL?

yes, you can also embed XQueries in SQL. Indeed, DB2 9 features support for standard SQL/XML functions that are frequently used to formulate hybrid queries in which SQL is the outermost (or top-level) language. You can use XMLExists function for this. This function lets you navigate to an element (or other type of node) within your XML document and test for a specific condition. When specified as part of the SQL
WHERE clause, XMLExists restricts the returned results to only those rows that contain an XML document that satisfies your specified condition (where the specified condition evaluates to "true").

[dilasing]

You can also use XMLQuery function which lets you project XML into the SQL result set returned from your SQL/XML query. It's commonly used to retrieve one or more elements from XML documents. Again, as you might imagine, the XMLQuery function takes an XQuery expression as input.

[ssahil11]

I have a problem while selecting XML data. The XML data elements have different namespaces and my query could not retrieve all records. How do I retrieve all XML records using XQuery. The XQuery I am trying out is as given below.

XQuery db2-fn:xmlcolumn('BUSINESSES.XMLINFO')/businessesname

[ssahil11]

I have a problem while selecting XML data. The XML data elements have different namespaces and my query could not retrieve all records. How do I retrieve all XML records using XQuery. The XQuery I am trying out is as given below.

XQuery db2-fn:xmlcolumn('BUSINESSES.XMLINFO')/businessesname

[dilasing]

The problem here is due to the company namespaces. You need to rewrite your XQuery as given below.

XQuery db2-fn:xmlcolumn('BUSINESSES.XMLINFO')/*:businessesname

[ssahil11]

Is there a way I can return XML data for selected namespace?

[dilasing]

DB2 treats XQuery as a first-class language, allowing users to write Xquery expressions directly rather than requiring that users embed or wrap XQueries in SQL statements. DB2's query engine processes XQueries natively, meaning that it parses, evaluates, and optimizes XQueries without ever translating them into SQL behind the scenes. You can check http://www.ibm.com/developerworks/db2/library/techarticle/dm-0603saracco2/ to know how to use several popular SQL/XML expressions to query DB2 XML data XQuery.

[ssahil11]

Can any one explain about the expressions that XQuery provides?

[dilasing]

XQuery provides several different kinds of expressions that may be combined in any way you like. Each expression returns a list of values that can be used as input to other expressions. The result of the outermost expression is the result of the query.
There are manly two important kinds of XQuery expressions:

i) FLWOR expressions
ii) Path expressions.

A FLWOR expression is much like a SELECT-FROM-WHERE expression in SQL -- it is used to iterate through a list of items and to optionally return something that is computed from each item. A Path expression, on the other hand, navigates through a hierarchy of XML elements and returns the elements that are found at the end of the path.

Like a SELECT-FROM-WHERE expression in SQL, an XQuery FLWOR expression may contain several clauses that begin with certain keywords. The following keywords are used to begin clauses in a FLWOR expression:
· for: Iterates through an input sequence, binding a variable to each input item in turn
· let: Declares a variable and assigns it a value, which may be a list containing multiple items
· where: Specifies criteria for filtering query results
· order by: Specifies the sort order of the result
· return: Defines the result to be returned
For example FLWOR expression to retrieve client fax data:
xquery for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/fax return $y
A Path expression in XQuery consists of a series of "steps," separated by slash characters. In its simplest form, each step navigates downward in an XML hierarchy to find the children of the elements returned by the previous step. Each step in a path expression may also contain a predicate that filters the elements that are returned by that step, retaining only elements that satisfy some condition.

For example Path expression to retrieve client fax data:

xquery
db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/fax

[ssahil11]

We are storing contact information of clients as XML document in a DB2 table. In the XML document we are storing zip code also. How can I retrieve the XML documents of contact information of persons who live under particular zip code?

[dilasing]

The XQuery where clause enables you to filter results based on the value of the zip element in your XML documents. Consider we stored below mentioned xml document in CONTACTINFO column of CLIENTS table.
<client>
<Address>
<street>5401 Julio Ave.</street>
<city>San Jose</city>
<state>CA</state>
<zip>95116</zip>
</Address>
<Phone>
<work>4084630000</work>
<home>4081111111</home>
<cell>4082222222</cell>
</phone>
<fax>4087778888</fax>
<email>[e-mail]</email>
</client>
Below example explains how to obtain only the addresses of the customers who live in zip code 95116 using FLWOR expression.
xquery
for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/Address
where $y/zip="95116"
return $y
The “for” clause binds the variable “$y” to each address in turn. The “where” clause contains a small path expression that navigates from each address to its nested zip element. The “where” clause is true (and the address is retained) only if the value of this zip element is equal to 95116.
The same result could be obtained by adding a predicate to the path expression,

xquery db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/Address[zip="95116"]

You can get the e-mail information for customers who live in a specific zip code in New York City (10011) or anywhere in the city of San Jose using following queries:

Using FLWOR expression:
xquery
for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client
where $y/Address/zip="10011" or $y/Address/city="San Jose"
return $y/email

Using path expression:
xquery
db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client[Address/zip="10011"
or Address/city="San Jose"]/email;

For more information you can follow the link:

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0604saracco/

[ssahil11]

I am using DB2 and storing the contact information of people in an XML column. I need to transform the resultant XML data in to HTML format. Can anyone help me?