From XML inside CLOB, to Oracle table with list of paths From XML inside CLOB, to Oracle table with list of paths oracle oracle

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;/