Retrieve xml values using SQL from xml column

Question

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>

are you sure e.event_data is of type XMLTYPE?

Marco Luzzara
– Marco Luzzara

2021-01-27 15:07:59 +00:00
Commented Jan 27, 2021 at 15:07 — Marco Luzzara
– Marco Luzzara, Commented Jan 27, 2021 at 15:07

Thomas Kirchhoff · Accepted Answer · 2021-01-27 15:44:42Z

1

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' );

edited Jan 27, 2021 at 15:44

answered Jan 27, 2021 at 15:17

Thomas Kirchhoff

1,1181 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Kirchhoff Over a year ago

Note: The EXTRACTVALUE function is deprecated.Use the XMLTABLE function.

Collectives™ on Stack Overflow

Retrieve xml values using SQL from xml column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related