0

I am using oracle version 11.2.0.4.0 I have a table FINMESSAGES with a CLOB column, columnname MESSAGE I want to extract an xml-element from the MESSAGE column and I did this with the following code (according to a solution in found here in this link)

SELECT
   x.*

FROM

   FINMESSAGES FM
   left join XMLTable(
   '/masterData'
   passing FM.MESSAGE
   columns
   latestversion varchar2(6) path 'version'
   )  x on (1=1)
;

but I got the following error :

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 818 Column: 12

Who knows what I do wrong ? thank you for helping me out

Community
  • 1
  • 1
herbie66
  • 1
  • 2

2 Answers2

1

Try this one:

SELECT
   x.*    
FROM

   FINMESSAGES FM
   left join XMLTable(
   '/masterData'
   passing XMLPARSE(CONTENT FM.MESSAGE WELLFORMED)
   columns
   latestversion varchar2(6) path 'version'
   )  x on (1=1)
;

or XMLPARSE(DOCUMENT FM.MESSAGE WELLFORMED)

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
1

You need to convert the CLOB value to an XMLType first by using the XMLTYPE.createxml() function. Replace passing FM.MESSAGE with passing XMLTYPE.createxml(FM.MESSAGE).

Here is my testcase:

create table xmltest 
( a number, b clob );

insert into xmltest values ( 1, '<masterData>asd</masterData>' );
insert into xmltest values ( 2, '<masterData><version>asd</version></masterData>' );
commit;

select XMLTYPE.createxml( b ) from xmltest;

SELECT
   x.*
FROM
   xmltest FM
   left join XMLTable(
   '/masterData'
   passing XMLTYPE.createxml( fm.b )
   columns
   latestversion varchar2(6) path 'version'
   )  x on (1=1)
;

Here is some documentation:

XMLTABLE

XMLTYPE

Mihail
  • 151
  • 4