Oracle SQL XMLCast extractvalue Example Tutorial

I stumbled across this new SQL function XMLCast when reading through the Oracle XML DB documentation today. This is a new function that is introduced in Oracle 11g and can be considered as a replacement to “extractValue” function. Oracle documentation has a confirmation that “extractValue” is deprecated with 11g Release 2. To understand how XMLCast performs better over extractvalue, I considered creating a simple table in Oracle with a XMLTYPE column in Oracle. To understand XMLCast usage, you need to create this table and insert the data as shown in the SQL script below:

 1 CREATE TABLE XMLCAST_TEST (
 2 U_ID NUMBER,
 3 U_VAL XMLTYPE
 4 )
 5 /
 6 INSERT INTO XMLCAST_TEST VALUES (1,XMLTYPE('<book>
 7 <main>
 8 <name>Maths</name>
 9 <pages>45</pages>
10 </main>
11 </book>'))
12 /
13 INSERT INTO XMLCAST_TEST VALUES (2,XMLTYPE('<book>
14 <main>
15 <name>Physics</name>
16 <pages>60</pages>
17 </main>
18 </book>'))
19 /
20 INSERT INTO XMLCAST_TEST VALUES (3,XMLTYPE('<book>
21 <main>
22 <name>Chemistry</name>
23 <pages>80</pages>
24 </main>
25 </book>'))
26 /
27 INSERT INTO XMLCAST_TEST VALUES (4,XMLTYPE('<book>
28 <main>
29 <name>Chemistry</name>
30 <pages>80</pages>
31 </main>
32 <main>
33 <name>Biology</name>
34 <pages>98</pages>
35 </main>
36 </book>'))

Now, if you try the SQL below in Oracle 11g R2, you will get the following error;

1 SELECT EXTRACTVALUE(U_VAL,'/book/main/pages') FROM XMLCAST_TEST

ORA-19025:EXTRACTVALUE returns value of only one node 

 The reason this error is observed is due to the fact that the fourth column [ column with ID: 4] has got two set of “main” tags and extractvalue cannot read both of these. This is a limitation in this function. XMLCast is a new function that is promising to avoid such kind of problems. It takes an expression that needs to be evaluated and casts the result into the following supported types: 

 • NUMBER
 • VARCHAR2
 • CHAR
 • CLOB
 • BLOB
 • REF XMLTYPE
 • any SQL date or time data type

1 SELECT XMLCast(XMLQuery('/book/main/pages' PASSING U_VAL RETURNING CONTENT) AS
2                VARCHAR2(100)) "REFERENCE"
3   FROM XMLCAST_TEST
4 

The results of this query is provided below [ Red line :- XML expression, Blue Box : Target data type ]

XMLCast SQL Example Query Output

If you look at the fourth column in this case, 80 and 98 are concatenated by XMLCast. Note that no error is raised when XMLCast is used. These values correspond to two of our nodes in the sample XML. If you try to cast the output of “name” node as “NUMBER” then it throws “invalid number” exception back on the screen.

That completes a basic introduction to XMLCast function in Oracle. We also explained the difference between this function to extractValue. In the next tutorial, we will see some advanced examples of using XMLCast with simple XML inputs. Stay connected to this blog for more.