Search code examples
xmloracle-databaseselectclob

select query for CLOB xml

I want to extract xml data stored in XMLDATA column my table FINAL_XML has following columns

IDENTIFIERTYPE | IDENTIFIER | DATE      | XMLDATA
-------------- | ---------- | --------- | -------
CONTACT        | 1          |0216-08-04 | CLOB, 4500 Bytes

Is there a way to write a select statement which would return actual xml tree?

thanks

Solution

  • You need to cast your CLOB to the XMLTYPE type:

    select xmltype(XMLDATA) from FINAL_XML

    This will give you an error if your CLOB field is not a valid XML.