Oracle extractValue Function not working

0
votes

I am trying to extract values from an XML Column in oracle. My Query is below:

select extractValue(COLUMN_NAME, '/parent/element1/')
from   TABLE_NAME where id = 111111;

However, I get the below error in my SQL Developer:

ORA-00932: inconsistent datatypes: expected - got - 00932. 00000 - "inconsistent datatypes: expected %s got %s"

Additional info:

  1. The columnn type I am trying to query is: VARCHAR2(2000 BYTE)
  2. The XML Contents in the column are very simple, a snippet is below:
<parent>
   <element1>value</element1>
   <element2>value</element2>
   <element3>value</element3>
</parent>
1
sqloracle

1 Answers

3
votes

The ExtractValue function can only be called on an XML data type. Try using the XMLTYPE function to convert your VARCHAR2 to an XML type:

select extractValue(XMLTYPE(COLUMN_NAME), '/parent/element1/')
from   TABLE_NAME where id = 111111;