This content has been marked as final. Show 4 replies
Database version (all digits please) ?
184.108.40.206.0 (Although I will need to do this on my work instance also which is 10.2.0.4)
Apologies for forgetting that in the first place.
220.127.116.11.0 (Although I will need to do this on my work instance also which is 10.2.0.4)That's gonna be a problem...
Direct CLOB projection is supported starting with 18.104.22.168, using XMLTable or XMLCast/XQuery functions (extractvalue, extract, xmlsequence are deprecated now) :
On prior versions, implicit conversions will occur to VARCHAR2 datatype, hence the limitation observed.
SELECT x.* FROM test_xml t , XMLTable( '/*/record' passing t.xml_data columns description clob path 'description' ) x ;
AFAIK you have two options on 10.2.0.4 :
1) Using Object-Relational storage, with the xdb:SQLType="CLOB" annotation.
2) Using the following trick :
SELECT dbms_xmlgen.convert(x.description.getClobVal(), 1) as description FROM test_xml t , XMLTable( '/*/record' passing t.xml_data columns description xmltype path 'description/text()' ) x ;
Works :) Thanks odie_63 !