Application development with DB2 pureXML - DB2 Express-C: The Developer for XML, PHP, C/C++, Java, and .NET - page 74

The FLWOR expression can be used to join XML values from different columns,
too. Example 2-16 gives an example query, which selects the reviews from the
MOVIEREVIEW table for all the movies from RR Pictures production.
Example 2-16 Joining two XML column values
Xquery for $id in
db2-fn:xmlcolumn('MOVIES.INFO')/movie[movie-details/production='RR
Pictures']/@id
let
$title:=db2-fn:xmlcolumn('MOVIES.INFO')/movie[@id=$id]/heading/title
return
<movie title = '{$title}'>
{
for $review in
db2-fn:xmlcolumn('MOVIEREVIEW.REVIEW')/movie[@id=$id]/reviews
return $review
}
</movie>@
SQL/XML functions
The XQuery and SQL interfaces interact with each other using SQL/XML
functions. Use these functions to embed the XQuery in the SQL/XML statement
or SQL in the XQuery statement.
xmlquery function
You typically use this function in the column list of the SELECT statement to
select a part of the document instead of the full value. This function is useful
when you need to select the part of the XML value based on the condition on a
rational column. Another significant advantage of this function is that it allows
passing the relational column to the XQuery via its passing by clause. The table
name for the column, to which the passing by clause refers, should be present in
the FROM clause of the SELECT query.
Example 2-17 on page 74 gives an example for xmlquery function. The function
selects the movie title with id= 123 .
Example 2-17 Using xmlquery function
select xmlquery('$d/movie/heading/title' passing movies.info as "d")
from movies where id=123
xmlquery function returns the single XML value for each row selected by the
SELECT statement. The result of the xmlquery function should be a single XML