sql - Oracle error ora-19025 Xmltype -

sql - Oracle error ora-19025 Xmltype -

i have imported xml oracle, using column type xmltype.

here construction of xml:

<release> <releaseid> <grid>a10320wa0003483489</grid> <icpn isean="false">3610151317086</icpn> <catalognumber namespace="upc">3610151317086</catalognumber> <proprietaryid namespace="adam_id">551247405</proprietaryid> </releaseid> <releasereference>r551247405</releasereference> <referencetitle> <titletext>tony bennett: 101 hits - left heart in san francisco</titletext> </referencetitle> <releaseresourcereferencelist> <releaseresourcereference>a551248137</releaseresourcereference> <releaseresourcereference>a551248136</releaseresourcereference> <releaseresourcereference>a551248070</releaseresourcereference> <releaseresourcereference>a551248074</releaseresourcereference> <releaseresourcereference>a551248041</releaseresourcereference> <releaseresourcereference>a551247836</releaseresourcereference> </releaseresourcereferencelist>

and here select tha using

select extractvalue(value (sr),'*/releaseid/isrc') isrc ,extractvalue( value (sr),'*/releaseid/grid' )as grid ,extractvalue( value (sr),'*/releaseid/icpn' )as icpn ,extractvalue( value (sr),'*/releaseid/catalognumber' )as catalognumber ,extractvalue( value (sr),'*/releaseid/proprietaryid' )as proprietaryid ,extractvalue( value (sr),'*/referencetitle/titletext' )as titletext ,extractvalue( value (sr),'*/releasetype' )as releasetype ,extractvalue( value (st),'*/releaseresourcereference' )as teste xml_ddex x, table(xmlsequence (extract(dados, '*/containedreleaselist/release' )))sr, table(xmlsequence(extract(value(sr),'*/releaseresourcereferencelist')))st

can help me ? thanks

based on guessed facts info , expected results, might query you're after:

select --+ no_merge(sr) sr.isrc, sr.grid, sr.icpn, sr.catalog_number, sr.proprietary_id, sr.title_text, sr.release_type, st.rel_res_ref teste xml_ddex x cross bring together xmltable( '*/containedreleaselist/release' passing x.dados columns rel_res_ref_list$ xmltype path '/release/releaseresourcereferencelist', isrc varchar2(200) path '/release/releaseid/isrc', grid varchar2(200) path '/release/releaseid/grid', icpn varchar2(200) path '/release/releaseid/icpn', catalog_number varchar2(200) path '/release/releaseid/catalognumber', proprietary_id varchar2(200) path '/release/releaseid/proprietaryid', title_text varchar2(200) path '/release/referencetitle/titletext', release_type varchar2(200) path '/release/releasetype' ) sr cross bring together xmltable( '/releaseresourcereferencelist/releaseresourcereference' passing sr.rel_res_ref_list$ columns rel_res_ref varchar2(200) path '/releaseresourcereference' ) st ;

i sorry no_merge hint. in test environment couldn't query work without hint; throwing ora-600 exceptions.

a test on partially guessed xml ...

with xml_ddex ( select xmltype('<?xml version="1.0" ?> <somerootelementiguessthereis> <containedreleaselist> <release> <releaseid> <grid>a10320wa0003483489</grid> <icpn isean="false">3610151317086</icpn> <catalognumber namespace="upc">3610151317086</catalognumber> <proprietaryid namespace="adam_id">551247405</proprietaryid> </releaseid> <releasereference>r551247405</releasereference> <referencetitle> <titletext>tony bennett: 101 hits - left heart in san francisco</titletext> </referencetitle> <releaseresourcereferencelist> <releaseresourcereference>a551248137</releaseresourcereference> <releaseresourcereference>a551248136</releaseresourcereference> <releaseresourcereference>a551248070</releaseresourcereference> <releaseresourcereference>a551248074</releaseresourcereference> <releaseresourcereference>a551248041</releaseresourcereference> <releaseresourcereference>a551247836</releaseresourcereference> </releaseresourcereferencelist> </release> <release> <releaseid> <grid>a10320wa0003483489</grid> <icpn isean="false">3610151317086</icpn> <catalognumber namespace="upc">3610151317086</catalognumber> <proprietaryid namespace="adam_id">551247405</proprietaryid> </releaseid> <releasereference>r551247405</releasereference> <referencetitle> <titletext>tony bennett: 102 hits - left head in fresno</titletext> </referencetitle> <releaseresourcereferencelist> <releaseresourcereference>a551248137</releaseresourcereference> <releaseresourcereference>a551248136</releaseresourcereference> <releaseresourcereference>a551248070</releaseresourcereference> <releaseresourcereference>a551248074</releaseresourcereference> <releaseresourcereference>a551248041</releaseresourcereference> <releaseresourcereference>a551247836</releaseresourcereference> </releaseresourcereferencelist> </release> </containedreleaselist> </somerootelementiguessthereis>' ) dados dual ) select --+ no_merge(sr) sr.isrc, sr.grid, sr.icpn, sr.catalog_number, sr.proprietary_id, sr.title_text, sr.release_type, st.rel_res_ref teste xml_ddex x cross bring together xmltable( '*/containedreleaselist/release' passing x.dados columns rel_res_ref_list$ xmltype path '/release/releaseresourcereferencelist', isrc varchar2(200) path '/release/releaseid/isrc', grid varchar2(200) path '/release/releaseid/grid', icpn varchar2(200) path '/release/releaseid/icpn', catalog_number varchar2(200) path '/release/releaseid/catalognumber', proprietary_id varchar2(200) path '/release/releaseid/proprietaryid', title_text varchar2(200) path '/release/referencetitle/titletext', release_type varchar2(200) path '/release/releasetype' ) sr cross bring together xmltable( '/releaseresourcereferencelist/releaseresourcereference' passing sr.rel_res_ref_list$ columns rel_res_ref varchar2(200) path '/releaseresourcereference' ) st ;

... yields ...

a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 101 hits - left heart in san francisco a551248137 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 101 hits - left heart in san francisco a551248136 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 101 hits - left heart in san francisco a551248070 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 101 hits - left heart in san francisco a551248074 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 101 hits - left heart in san francisco a551248041 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 101 hits - left heart in san francisco a551247836 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 102 hits - left head in fresno a551248137 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 102 hits - left head in fresno a551248136 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 102 hits - left head in fresno a551248070 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 102 hits - left head in fresno a551248074 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 102 hits - left head in fresno a551248041 a10320wa0003483489 3610151317086 3610151317086 551247405 tony bennett: 102 hits - left head in fresno a551247836

sql xml oracle11g