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 0 comments

 You have often to count number of occurrences of one string X in text.

You can use only two methods to do that.

Here is example for PL/SQL, you can use this trick in all languages just use right length and replace function. X is 'T' and the text is 'TEXT'

count  := (length('TEXT')-length( replace('TEXT','T','' ))) / length('T')

result is 2

# posted by Domen Dolar @ 9:06 PM 0 comments