Oracle XMLExists Function Example

We earlier discussed existsNode function in Oracle with examples. During our discussion, we mentioned that existsNode is deprecated with 11g Release 2 version of Oracle and Oracle recommends using XMLExists function instead. In this blog post, we will see what XMLExists is all about and run through some examples of using XMLExists against an XMLTYPE column in Oracle. To start with, consider a very simple set of XMLs as provided below;

<Laptop> <Notebook> <Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title> <Price>450</Price> </Notebook> <Notebook> <Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title> <Price>800</Price> </Notebook> </Laptop>

<Laptop> <Notebook> <Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title> <Price>700</Price> </Notebook> <Notebook> <Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title> <Price>1200</Price> </Notebook> </Laptop>

Make sure that you push these two XMLs to our Orders table in the XMLTYPE column ORDERDATA. We have two rows of XMLData with us now and we can run some XMLExists Query on that to understand XMLExists better.

Objective: List only those rows that have a brand "Sony"

SQL: The SQL to achieve this objective with XMLExists is provided below. As shown in the SQL, we use XMLExists, where we get into the attribute of the Title Tag i.e. Brand with value of Sony. The PASSING clause of XMLExists tells the Query the exact input XMLDATA that needs to be used for the check.

SELECT ORDERDATA FROM ORDERS
WHERE
XMLExists('/Laptop/Notebook/Title[@Brand="Sony"]' /* Catch rows with Sony Brand */
PASSING ORDERDATA)
 

The output of this SQL will have only one row i.e.the XML that has the Sony as an attribute. As another example of using XMLExists, to list all the rows where at-least one of the Price tag has a value less than 500, the SQL provided below can be used

SELECT ORDERDATA FROM ORDERS
WHERE
XMLExists('/Laptop/Notebook[Price<500]' PASSING ORDERDATA)

Some important differences between XMLExists and ExistsNode are documented below

XMLExists	ExistsNode
Returns TRUE or FALSE	Returns 0 or 1
Cannot be directly used in a Query Select List, but can be used with a CASE expression	Can be used in a Query Select List
Supports XQuery Expressions	No support for XQuery Expressions
Latest	Deprecated with 11g R2

Row # 2 in the difference table indicates that XMLExists can be used with a CASE expression. An example of such an usage is provided below:

SELECT CASE
WHEN
XMLExists('/Laptop/Notebook[Price<=600][1]'
PASSING ORDERDATA) THEN
EXTRACT(ORDERDATA,'/Laptop/Notebook[1]/Title')
.getstringval()
ELSE
'NOT FOUND'
END
FROM ORDERS

In the SQL above, we check if the Price of the first Notebook element is less than or equal to 600. If so, then we use EXTRACT to dump the Title value of the "Notebook" element. Otherwise we dump 'NOT FOUND' in the output. This SQL gives an output as shown below (HP for first row, and NOT FOUND for the second row)

<Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>

NOT FOUND

This is a simple example for XMLExists function. In the upcoming blog posts, we will see how to use XQuery in XMLExists function.