Hints and tricks for Oracle and other stuff...

 If you have in XML LIST more than 300.000 items you will get error "ORA-31186: Document contains too many nodes"

select
      extractvalue(value(x), '//LIST/ITEM') ITEM,
from  table_with_xml t
   ,  table(xmlsequence(extract(t.xml_col,'//LIST'))) x  

If you have like 450.000 items, you can split XML like ... in two parts and combine them with union all

select
      extractvalue(value(x), '//LIST/ITEM') ITEM,
from  (select 
          substr(xml_col, 1 , instr(xml_col, '</ITEM>',1 , 
          250000  -- first 250.000 items
          ))||'/ITEM></LIST>' xml_col
       from table_with_xml
       ) t
   ,  table(xmlsequence(extract(t.xml_col,'//LIST'))) x  
union all
select
      extractvalue(value(x), '//LIST/ITEM') ITEM,
from  (select
          '<LIST><ITEM>'||  
          substr(xml_col, instr(xml_col, '</ITEM>',1 ,
          200000  -- last 200.000 items
          )+length('</ITEM>') ) xml_col
       from table_with_xml
       ) t
   ,  table(xmlsequence(extract(t.xml_col,'//LIST'))) x
;

# posted by Domen Dolar @ 9:35 PM