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

otherwise, it returns false . xmlexists also uses the passing b y clause to pass the
relational and XML column to the xquery and is useful when the filter condition is
based on the comparison of the relational column with the XML node value.
xmlexists is generally used in the WHERE clause of the SELECT statement.
Example 2-19 shows you a query, which returns the IDs for all the movies with
genres Drama, Romance, and Remake.
Example 2-19 Using xmlexists
select id from movies where
xmlexists('$movie/movie/movie-details[genres="Drama, Romance and
Remake"]' passing movies.info as "movie")@
Use xmlexists in conjuction with the xmlquery function to select the part of the
XML document based on a comparison between a node value and relation
column value.
Example 2-20 shows you a query, which selects the movie name and the
corresponding reviews as two separate columns of the result set.
Example 2-20 Using xmlexists in conjuction with xmlquery
select xmlquery('$d/movie/heading/title' passing movies.info as "d"),
xmlquery('$d/movie/reviews' passing moviereview.review as "d") from
moviereview, movies where
xmlexists('$d/movie[@id=$p]' passing moviereview.review as "d",
movies.id as "p")
xmlcast function
The xmlcast function is used to cast the XML value to other relational data types.
It is equivalent to the existing cast function for relational data. xmlcast first
resolves the XML value to an XQuery atomic data type and then does the
conversion to the relational data type. Example 2-21 shows the usage of the
xmlcast function. The resulting columns in Example 2-20 are of XML type. Using
xmlcast function, the values can be casted to other data types.
Example 2-21 Using xmlcast
select xmlcast(xmlquery('$d/movie/heading/title' passing movies.info as
"d") as varchar(20)),
xmlcast(xmlquery('$d/movie/reviews' passing moviereview.review as "d")
as varchar(1000)) from moviereview, movies where
xmlexists('$d/movie[@id=$p]' passing moviereview.review as "d",
movies.id as "p");