From XML inside CLOB, to Oracle table with list of paths
UPD I found quite big xml file (140 KB). My sistem: notebook with core i5 processor (2400 MHz), oracle 12c inside a virtual machine, time to process - 0.38 seconds. This method is the only alternative that I know. Example of xml here I found at w3schools.com.
declare xml_str clob := q'[<?xml version="1.0" encoding="UTF-8"?><CATALOG> <CD> <TITLE>Empire Burlesque</TITLE> <ARTIST>Bob Dylan</ARTIST> <COUNTRY>USA</COUNTRY> <COMPANY>Columbia</COMPANY> <PRICE>10.90</PRICE> <YEAR>1985</YEAR> </CD> <CD> <TITLE>Hide your heart</TITLE> <ARTIST>Bonnie Tyler</ARTIST> <COUNTRY>UK</COUNTRY> <COMPANY>CBS Records</COMPANY> <PRICE>9.90</PRICE> <YEAR>1988</YEAR> </CD></CATALOG>]';v_doc dbms_xmldom.domdocument;node dbms_xmldom.domnode;txt varchar2(4000);type t_list is table of number index by varchar2(4000);v_list t_list; procedure enum_nodes(n dbms_xmldom.domnode, tag_name varchar2) is chn dbms_xmldom.domnode; nl dbms_xmldom.domnodelist; begin nl := dbms_xmldom.getchildnodes(n); for i in 0..dbms_xmldom.getlength(nl) loop chn := dbms_xmldom.item(nl, i); if dbms_xmldom.getnodetype(chn) = 1 then enum_nodes(chn, tag_name || dbms_xmldom.getnodeName(chn) || '/'); elsif dbms_xmldom.getnodetype(chn) = 3 then v_list(tag_name || dbms_xmldom.getnodevalue(chn)) := 1; end if; end loop; end;begin v_doc := dbms_xmldom.newdomdocument(xml_str); node := dbms_xmldom.makenode(v_doc); enum_nodes(node, '/'); txt := v_list.first; while txt is not null loop dbms_output.put_line(txt); txt := v_list.next(txt); end loop;end;/