Read XMLTYPE from Oracle table

[former_member192939]

Anyone has success reading Oracle data type XMLTYPE.

select all query returns all other columns but the XMLTYPE column is blank

MII - 12.1.9

Thanks

Ajay

Edited by: Ajay Malempati on Dec 8, 2011 10:50 AM

[jcgood25 · Accepted solution]

Have you tried doing a TO_CHAR(XMLColumn) AS StringXMLColumn on the column to see if string conversion does the trick?

[former_member192939 · Accepted solution]

Yes Jeremy

I have tried ..

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got -

Thanks

Ajay

[Former Member · Accepted solution]

Ajay,

this sample select statement works on Oracle 9.2.x.

SELECT 
          t.YOURXMLCOLUMN.extract('*').getStringVal() as myXMLColumn
FROM
          TABLENAME t

Let us know the result.

BR

Pedro Iglesias

[former_member192939 · Accepted solution]

Wow ..

Thank you for the tip Pedro .. Aliasing the table name worked

I was trying ..

SELECT

TABLENAME.YOURXMLCOLUMN.extract('*').getStringVal() as myXMLColumn

FROM

TABLENAME

Got the following error.

Error is java.sql.SQLException: ORA-00904: "TABLENAME"."YOURXMLCOLUMN"."EXTRACT": invalid identifier ..

Tip: Alias table name in the query.

Thank you

Ajay.