2 Replies Latest reply on Aug 8, 2019 6:05 PM by nedjo

    PL/SQL: Parsing XML and inserting into relational tables

    nedjo

      I have the following XML:

       

      <Orders>
          <Order order_num="testeewrr" employee="jaaaaaa" createdDT="2019-07-17T13:06:00" modifiedDT="2019-08-06T10:26:15" deletedDT="" state="OPENED" ts="Bel" nni="Izvod 4">
            <Meter xmlID="1" createdDT="2019-07-17T13:06:00" modifiedDT="2019-07-22T13:48:04" deletedDT="" vendor="ENEL" model="DB2" serial="123424" />
            <Meter xmlID="2" createdDT="2019-07-17T13:06:20" modifiedDT="2019-07-22T13:48:04" deletedDT="" vendor="EWG" model="E311N2A20S" serial="45544545" />
            <Meter xmlID="3" createdDT="2019-07-17T13:06:39" modifiedDT="2019-07-18T14:24:45" deletedDT="" vendor="Meter&amp;Control" model="SM405D" serial="234324234" />
            <Meter xmlID="4" createdDT="2019-07-17T13:50:57" modifiedDT="2019-07-17T13:50:57" deletedDT="2019-07-18T14:08:34" vendor="ENEL" model="DB2" serial="" />
            <Meter xmlID="5" createdDT="2019-07-17T14:24:12" modifiedDT="2019-07-17T14:24:12" deletedDT="2019-07-18T14:08:36" vendor="ENEL" model="DB2" serial="" />
          </Order>
          <Order order_num="retert" employee="23432434" createdDT="2019-07-17T14:25:17" modifiedDT="2019-07-22T13:21:48" deletedDT="" state="CLOSED" ts="Crkveno brdo" nni="Izvod 2">
            <Meter xmlID="2" createdDT="2019-07-17T14:25:19" modifiedDT="2019-07-22T13:21:23" deletedDT="" vendor="ENEL" model="DB2" serial="10006502">
              <Test xmlID="1" createdDT="2019-07-22T13:21:23" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 13:19:47" />
            </Meter>
          </Order>
          <Order order_num="121212" employee="sasasa" createdDT="2019-07-22T11:30:16" modifiedDT="2019-08-06T10:35:31" deletedDT="" state="OPENED" ts="Autoservis" nni="Izvod 1">
            <Meter xmlID="1" createdDT="2019-07-22T11:31:00" modifiedDT="2019-08-06T10:16:41" deletedDT="2019-07-24T13:52:04" vendor="ENEL" model="DB2" serial="">
              <Test xmlID="1" createdDT="2019-07-22T12:48:10" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:46:30" />
              <Test xmlID="2" createdDT="2019-07-22T12:48:54" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:47:14" />
              <Test xmlID="3" createdDT="2019-07-22T12:49:28" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:47:48" />
              <Test xmlID="4" createdDT="2019-07-22T12:51:47" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:50:07" />
              <Test xmlID="5" createdDT="2019-07-25T15:50:19" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 25.07.2019. 15:48:40" />
            </Meter>
            <Meter xmlID="2" createdDT="2019-07-22T11:31:17" modifiedDT="2019-08-06T10:16:42" deletedDT="2019-07-24T13:52:07" vendor="ENEL" model="DB2" serial="">
              <Test xmlID="1" createdDT="2019-07-22T12:48:16" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:47:18" />
              <Test xmlID="2" createdDT="2019-07-22T12:48:57" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:47:59" />
              <Test xmlID="3" createdDT="2019-07-22T12:49:31" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:48:33" />
              <Test xmlID="4" createdDT="2019-07-22T12:51:52" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:50:55" />
            </Meter>
            <Meter xmlID="3" createdDT="2019-08-06T10:16:42" modifiedDT="2019-08-06T10:16:42" deletedDT="" vendor="ENEL" model="DB2" serial="" />
            <Meter xmlID="4" createdDT="2019-08-06T10:16:42" modifiedDT="2019-08-06T10:16:51" deletedDT="" vendor="ENEL" model="DB2" serial="" />
          </Order>
          <Order order_num="hola" employee="hermanos" createdDT="2019-07-22T13:48:23" modifiedDT="2019-08-06T10:19:14" deletedDT="" state="OPENED" ts="Autoservis" nni="Izvod 1">
            <Meter xmlID="4" createdDT="2019-07-22T13:48:23" modifiedDT="2019-08-06T10:19:20" deletedDT="2019-07-24T13:08:49" vendor="ENEL" model="DB2" serial="" />
            <Meter xmlID="5" createdDT="2019-07-24T13:08:36" modifiedDT="2019-08-06T10:19:20" deletedDT="2019-07-24T13:08:51" vendor="ENEL" model="DB2" serial="" />
            <Meter xmlID="6" createdDT="2019-07-24T13:08:45" modifiedDT="2019-07-24T13:51:00" deletedDT="2019-07-24T13:08:53" vendor="Mikroelektronika" model="MET410E34PI05IM006UN230" serial="11050507" />
            <Meter xmlID="7" createdDT="2019-07-24T13:09:02" modifiedDT="2019-07-24T13:51:00" deletedDT="2019-07-24T13:09:42" vendor="Mikroelektronika" model="MET410E34II05IM006UN058" serial="11050508" />
            <Meter xmlID="8" createdDT="2019-07-24T13:09:11" modifiedDT="2019-07-24T13:09:19" deletedDT="2019-07-24T13:09:44" vendor="ENEL" model="DB2" serial="A12345678" />
            <Meter xmlID="9" createdDT="2019-07-24T13:09:19" modifiedDT="2019-07-24T13:09:41" deletedDT="2019-07-24T13:09:45" vendor="ENEL" model="DB2M" serial="A12345678" />
            <Meter xmlID="10" createdDT="2019-07-24T13:09:46" modifiedDT="2019-07-24T13:12:09" deletedDT="2019-07-24T13:12:21" vendor="ENEL" model="DB2" serial="" />
            <Meter xmlID="11" createdDT="2019-07-24T13:12:34" modifiedDT="2019-07-24T13:15:38" deletedDT="2019-07-24T13:46:39" vendor="ENEL" model="DB2M" serial="A12345678" />
            <Meter xmlID="12" createdDT="2019-07-24T13:12:49" modifiedDT="2019-07-24T13:15:40" deletedDT="2019-07-24T13:46:39" vendor="Meter&amp;Control" model="SM405D" serial="12345678" />
            <Meter xmlID="13" createdDT="2019-07-24T13:13:02" modifiedDT="2019-07-24T13:13:06" deletedDT="2019-07-24T13:46:40" vendor="ENEL" model="DB2" serial="A12345678" />
            <Meter xmlID="14" createdDT="2019-07-24T13:13:06" modifiedDT="2019-07-24T13:13:08" deletedDT="2019-07-24T13:13:31" vendor="ENEL" model="DB2" serial="A12345678" />
            <Meter xmlID="15" createdDT="2019-07-24T13:13:08" modifiedDT="2019-07-24T13:13:27" deletedDT="2019-07-24T13:13:28" vendor="ENEL" model="DB2" serial="A12345678" />
            <Meter xmlID="17" createdDT="2019-07-24T13:40:59" modifiedDT="2019-07-24T13:41:02" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050501" />
            <Meter xmlID="18" createdDT="2019-07-24T13:41:02" modifiedDT="2019-07-24T13:41:09" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050502" />
            <Meter xmlID="19" createdDT="2019-07-24T13:41:09" modifiedDT="2019-07-24T13:41:12" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050504" />
            <Meter xmlID="20" createdDT="2019-07-24T13:41:12" modifiedDT="2019-07-24T13:41:15" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050505" />
            <Meter xmlID="21" createdDT="2019-07-24T13:41:15" modifiedDT="2019-07-24T13:41:18" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050506" />
            <Meter xmlID="22" createdDT="2019-07-24T13:41:18" modifiedDT="2019-07-24T13:41:21" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34PI05IM006UN230" serial="11050507" />
            <Meter xmlID="23" createdDT="2019-07-24T13:41:21" modifiedDT="2019-07-24T13:41:21" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34II05IM006UN058" serial="11050508" />
            <Meter xmlID="1" createdDT="2019-07-24T13:50:50" modifiedDT="2019-07-25T13:38:18" deletedDT="" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050501">
              <Test xmlID="1" createdDT="2019-07-25T13:39:21" testCount="4" successCount="0" test1="ZenDirect failed to identify meter  [11050501]" test2="Нема конекције" test3="Нема конекције" test4="Нема конекције" />
            </Meter>
            <Meter xmlID="2" createdDT="2019-07-24T13:50:53" modifiedDT="2019-07-24T13:50:56" deletedDT="" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050502" />
            <Meter xmlID="3" createdDT="2019-07-24T13:50:56" modifiedDT="2019-07-24T13:50:57" deletedDT="" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050504" />
          </Order>
          <Order order_num="eneltest" employee="sasss" createdDT="2019-07-25T13:52:36" modifiedDT="2019-07-25T13:53:23" deletedDT="" state="CLOSED" ts="Autoservis" nni="Izvod 1">
            <Meter xmlID="1" createdDT="2019-07-25T13:52:58" modifiedDT="2019-07-25T13:52:58" deletedDT="" vendor="ENEL" model="DB2" serial="10006502">
              <Test xmlID="1" createdDT="2019-07-25T13:53:16" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 25.07.2019. 13:51:37" />
            </Meter>
            <Meter xmlID="2" createdDT="2019-07-25T13:52:58" modifiedDT="2019-07-25T13:53:22" deletedDT="" vendor="ENEL" model="DB2M" serial="10006501">
              <Test xmlID="1" createdDT="2019-07-25T13:53:21" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 25.07.2019. 13:52:25" />
            </Meter>
          </Order>
        </Orders>

      How to load this XML into relational tables so that SELECT can be done?

      IMPORTANT: I am writing a PLSQL procedure for this, I don't want SQL query.

       

      Here is my procedure:

       

        procedure insert_xml_by_parts(p_header_id in number,

                             p_contents_part1 in varchar2,

                             p_contents_part2 in varchar2,

                             p_contents_part3 in varchar2,

                             p_contents_part4 in varchar2,

                             p_contents_part5 in varchar2,

                             p_contents_part6 in varchar2,

                             p_contents_part7 in varchar2,

                             p_contents_part8 in varchar2,

                             p_contents_part9 in varchar2,

                             p_contents_part10 in varchar2,

                             p_contents_part11 in varchar2,

                             p_contents_part12 in varchar2,

                             p_contents_part13 in varchar2,

                             p_contents_part14 in varchar2,

                             p_contents_part15 in varchar2,

                             p_contents_part16 in varchar2,

                             p_contents_part17 in varchar2,

                             p_contents_part18 in varchar2,

                             p_contents_part19 in varchar2,

                             p_contents_part20 in varchar2,

                            

                             o_cur_results out sys_refcursor,

                             result_code out number) is

                            

      l_clob clob;

      l_id number;

       

        procedure SetError(pErrCode number) is

       

        -- A centralised sub proc could allow for a quick change to raise_application_error at any time.

       

        begin

       

          result_code   := pErrCode;

       

       

        end;

       

      begin

       

        select count(*) into l_id from log_sync_calls_headers

      where log_sync_calls_headers.id=p_header_id;

       

        case

       

          when (p_header_id is null) or (l_id <= 0)         then SetError(9500);

         

      else   

                  

      DBMS_LOB.CREATETEMPORARY(l_clob,true);

      if p_contents_part1 is not null then       

      dbms_lob.append(l_clob, p_contents_part1);

      end if;

      if p_contents_part2 is not null then

      dbms_lob.append(l_clob, p_contents_part2);

      end if;

      if p_contents_part3 is not null then

      dbms_lob.append(l_clob, p_contents_part3);

      end if;

      if p_contents_part4 is not null then

      dbms_lob.append(l_clob, p_contents_part4);

      end if;

      if p_contents_part5 is not null then

      dbms_lob.append(l_clob, p_contents_part5);

      end if;

      if p_contents_part6 is not null then

      dbms_lob.append(l_clob, p_contents_part6);

      end if;

      if p_contents_part7 is not null then

      dbms_lob.append(l_clob, p_contents_part7);

      end if;

      if p_contents_part8 is not null then

      dbms_lob.append(l_clob, p_contents_part8);

      end if;

      if p_contents_part9 is not null then

      dbms_lob.append(l_clob, p_contents_part9);

      end if;

      if p_contents_part10 is not null then

      dbms_lob.append(l_clob, p_contents_part10);

      end if;

      if p_contents_part11 is not null then

      dbms_lob.append(l_clob, p_contents_part11);

      end if;

      if p_contents_part12 is not null then

      dbms_lob.append(l_clob, p_contents_part12);

      end if;

      if p_contents_part13 is not null then

      dbms_lob.append(l_clob, p_contents_part13);

      end if;

      if p_contents_part14 is not null then

      dbms_lob.append(l_clob, p_contents_part14);

      end if;

      if p_contents_part15 is not null then

      dbms_lob.append(l_clob, p_contents_part15);

      end if;

      if p_contents_part16 is not null then

      dbms_lob.append(l_clob, p_contents_part16);

      end if;

      if p_contents_part17 is not null then

      dbms_lob.append(l_clob, p_contents_part17);

      end if;

      if p_contents_part18 is not null then

      dbms_lob.append(l_clob, p_contents_part18);

      end if;

      if p_contents_part19 is not null then

      dbms_lob.append(l_clob, p_contents_part19);

      end if;

      if p_contents_part20 is not null then

      dbms_lob.append(l_clob, p_contents_part20);

       

      end if;

       

         

             

      insert_xml(p_header_id, l_clob, o_cur_results, result_code );

       

      dbms_lob.freetemporary(l_clob);

       

      end case;

       

      commit;

       

              exception      when others then

              result_code :=9501;

              rollback;

              pkg_common.insert_log_record(p_source => 'insert_xml_by_parts',

                      p_type => 'Er',

                      p_message => sqlerrm);

       

      end;

       

       

      Here, after inserting CLOB in a table, I need to parse the XML and insert data to parsed tables. How to do this?