CODEWITHSUNDEEP
sqlxmloracle-databaseextract-value
user15092305
user15092305

Reputation: 1

Retrieve xml values using SQL from xml column

I am trying to retrieve xml value from a column with data stored in xml format using below query in Oracle SQL Developer. But getting this error:

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

Data type of column is CLOB

 SELECT id, 
   extractValue(e.event_data, '/DocumentRequest/RequestUserName')
   "Number of Docks"
   FROM table e

eg:

<DocumentRequest>
    <RequestUserName>XXXX</RequestUserName>
    <RequestDateTime>XXXX</RequestDateTime>
    <DocSequence>1</DocSequence>

Upvotes: 0

Views: 653

Answers (1)

Thomas Kirchhoff
Thomas Kirchhoff

Reputation: 998

If the XML is stored as CLOB, then take XMLTYPE:

SELECT id,
       EXTRACTVALUE( XMLTYPE(e.event_data), '/DocumentRequest/RequestUserName') AS "Number of Docks"
  FROM table e;

Query which uses XMLTABLE:

SELECT id,
       col1 AS "Number of Docks"
  FROM table e, XMLTABLE( '/DocumentRequest'
                  PASSING XMLTYPE(e.event_data)
                  COLUMNS col1 PATH 'RequestUserName' );

Upvotes: 1

Related Questions