CODEWITHSUNDEEP
xmloracle-databaseselectclob
ivanz
ivanz

Reputation: 815

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

Upvotes: 0

Views: 841

Answers (2)

XING
XING

Reputation: 9886

Please see below how to read xml data from a column of xmltype:

SELECT x.xml_data.getClobVal()
FROM   xml_tab x;

If your XMLDATA column is of xmltype datatype then you query goes like:

SELECT  xmldata.getClobVal()
    FROM  FINAL_XML;

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 1

Related Questions