EXTRACTVALUE and datatype errors

Question:

I'm getting this error while extracting a value with extractvalue:

SQL> SELECT
2 EXTRACTVALUE(xmldoc, '/EMP/ROW/EMPNO'),
3 EXTRACTVALUE(xmldoc, '/EMP/ROW/ENAME')
4 FROM
5 xml_tab;
EXTRACTVALUE(xmldoc, '/EMP/ROW/ENAME')
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected - got -


This is the xml file content stored in a table as xml_tab in clob column :

emp>
<row>
<empno>3</empno>
<ename>ra</ename>
</row>
<row>
<empno>9</empno>
<ename>a</ename>
</row>
<row>
<empno>6</empno>
<ename>r</ename>
</row>
</emp>


Answer:

 Use this command:

EXTRACTVALUE(xmltype(xmldoc), '/EMP/ROW/EMPNO')

The extractvalue error, ORA-00932, was being thrown because you had the wrong datatype.

Documentation provides this information:

The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node.

You may be experiencing a Bug when ORA-00932 is thrown with EXTRACTVALUE:

For more information on ORA-00932, you may want to check out the following link:

http://www.dba-oracle.com/sf_ora_00932_inconsistent_datatypes_expected_string_got_string.htm

This extractvalue function accepts an XML document or an XML element and an XPath expression as its arguments and returns the scalar value of the expression’s resultant node.

The prototype of the EXTRACTVALUE function is shown below,

The below SQL statement extracts the scalar value from the node /Rack1/Name as shown,

Result:

The Ironman