Oracle 10g: Extract data (select) from XML (CLOB Type)
Try using xmltype.createxml(xml).
As in,
select extract(xmltype.createxml(xml), '//fax').getStringVal() from mytab;It worked for me.
If you want to improve or manipulate even further.
Try something like this.
Select *from xmltable(xmlnamespaces('some-name-space' as "ns", 'another-name-space' as "ns1", ), '/ns/ns1/foo/bar' passing xmltype.createxml(xml) columns id varchar2(10) path '//ns//ns1/id', idboss varchar2(500) path '//ns0//ns1/idboss', etc.... ) nice_xml_tableHope it helps someone.