0

I am trying to write an xquery to read values from an xml and display in table format(XML table), instead of going to an xml and searching for value of a field.

I am got the below error : (The location mentioned is the “xmlcolumnname” that is passed in the sql query. “ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s"

Request to please help provide solution.

I am using a SQL developer editor to write the sql and the database is a ORACLE 11g database. Below is a sample partial XML and my sql.

<typ:first-response xmlns:i18n="http://www.w3.org/2005/09/ws-i18n"     xmlns:typ="url">
  <typ:global-instance>
    <typ:code_of_car type="boolean" inferred="true">
      <typ:text-val>QP</typ:text-val>
    </typ:code_of_car>
</typ:global-instance>
</typ:first-response>

SQL:

 SELECT column1, column2,column3,x.*
 FROM table,
 XMLTABLE('$M/*:first-response/*:global-instance/*:code_of_car/' 
 PASSING    xmlcolumnname  AS "M"
 COLUMNS
 text CHAR(100) PATH '*:text-val') 
 AS x ;
tushar9898
  • 11
  • 1
  • 5
  • This is not XQuery, just plain old Xpath, no? Please show the definition of "table". – OldProgrammer Oct 13 '15 at 01:30
  • @OldProgrammer: Sorry, can't share the actual table name and columns. xmlcolumnname datatype is a CLOB. I got the resolution. I did a casting by writing XMLTYPE(xmlcolumnname) and it worked fine :) – tushar9898 Oct 13 '15 at 17:18

0 Answers0