Oracle

Using the XMLExists SQL/XML Function in a SELECT List

by Yuli Vasiliev

Using the XMLExists SQL/XML Function in a SELECT List

By Yuli Vasiliev

SQL/XML function XMLExists was introduced in Oracle Database 11g to eventually replace Oracle’s function existsNode. Unlike the existsNode though, XMLExists cannot be used in the SELECT list directly. However, you can still do that by wrapping it in a CASE expression. This article provides an example on how this can be done.

To walk through the article sample, you will need to connect to an Oracle database as the OE user – OE is a demonstration schema that should come with your Oracle database installation. Once you are connected, you can issue the following query containing: XMLExists, XMLCast, and XMLQuery within the CASE expression in the SELECT list of the query:

SELECT DISTINCT CASE
WHEN
XMLExists(‘/PurchaseOrder[SpecialInstructions=”Air Mail”]’
PASSING OBJECT_VALUE) THEN
XMLCast(XMLQuery(‘/PurchaseOrder/Reference’ PASSING OBJECT_VALUE RETURNING CONTENT) AS VARCHAR2(100))
END as “ORDER_REFERENCE”
FROM purchaseorder;

The output of this query should look like this (output truncated to save space):

ORDER_REFERENCE
———————————

ACABRIO-20100417155650599PDT
NKOCHHAR-20100420165002744PDT
TFOX-20100409122744575PDT
AHUTTON-20100824183827493PDT
DOCONNEL-20100831195618831PDT
DRAPHEAL-20100824213229320PDT
GHIMURO-20100825145710763PDT
JKING-20100806194957888PDT
LSMITH-20100823212341407PDT
MROGERS-20100821123301915PDT
…

Note the use of the DISTINCT keyword followed by the CASE expression. In this particular example, DISTINCT is used to get rid of the NULL-valued rows generated by the CASE expression for the purchase orders whose element SpecialInstructions contains a value other than Air Mail. Strictly speaking, the NULL-valued rows in the above query are reduced to a single one, since what DISTINCT actually does is removes duplicate rows from the result set.

It is interesting to note that the above query can be rewritten so that XMLExists appears in the WHERE clause and there is no CASE expression at all, producing the same results:

SELECT XMLCast(XMLQuery(‘/PurchaseOrder/Reference’ PASSING OBJECT_VALUE RETURNING CONTENT) AS VARCHAR2(100)) AS “ORDER_REFERENCE”
FROM purchaseorder
WHERE XMLExists(‘/PurchaseOrder[SpecialInstructions=”Air Mail”]’
PASSING OBJECT_VALUE);

About the Author

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, and service-oriented architecture (SOA). He is the author of Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting (Packt, 2010) as well as a series of other books on the Oracle technology.