1 2 Previous Next 16 Replies Latest reply: Oct 25, 2012 9:37 AM by 964494 Go to original post RSS
      • 15. Re: Need to insert values into a table from a XML file
        Here's a simpler approach.

        Instead of implementing the extraction part inside the function, we'll just create a standalone pipelined function that retrieves each XML document (as XMLType) in a separate row.
        We can then apply XMLTable over this set of documents.

        That introduces a lot more flexibility because we can change the way we query the data without having to modify the PL/SQL code.
        create or replace function splitXMLDocument (p_directory in varchar2, p_filename in varchar2)
        return xmlsequencetype pipelined
          nb_rec          number := 1;
          tmp_xml         clob;
          tmp_file        clob;
          dbms_lob.createtemporary(tmp_file, true);
          tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);
          tmp_file := regexp_replace(tmp_file, '<!DOCTYPE[^>]+>');
            tmp_xml := regexp_substr(tmp_file,'<\?xml[^?]+\?>\s*<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
            exit when length(tmp_xml) = 0;
            nb_rec := nb_rec + 1;
            pipe row ( xmltype(tmp_xml) );
          end loop;
        SQL> select x.wid, x.waccess
          2  from table(splitXMLDocument('TEST_DIR', 'XFileHandler_test.xml')) t
          3     , xmltable(
          4         '/House/Warehouse'
          5         passing t.column_value
          6         columns wid     number(2)   path 'WarehouseId'
          7               , waccess varchar2(5) path 'WaterAccess'
          8       ) x
          9  ;
        --- -------
          1 true
          2 true
          3 false
          4 true
          5 true
          6 false
          7 true
          8 true
          9 false
        9 rows selected
        (NB : I've finally used a regexp to remove any DTD declaration)
        • 16. Re: Need to insert values into a table from a XML file
          Thanks Odie for all your advice and tips. I've managed to get it working.

          Time to wrap a stored procedure round the whole lot.
          1 2 Previous Next