This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 25, 2012 7:37 AM by 964494 Go to original post RSS
  • 15. Re: Need to insert values into a table from a XML file
    odie_63 Guru
    Currently Being Moderated
    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
    is
    
      nb_rec          number := 1;
      tmp_xml         clob;
      tmp_file        clob;
    
    begin
    
      dbms_lob.createtemporary(tmp_file, true);
      tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);
      tmp_file := regexp_replace(tmp_file, '<!DOCTYPE[^>]+>');
    
      loop
    
        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;
    
      dbms_lob.freetemporary(tmp_file);
      return;
    
    end;
    /
    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  ;
     
    WID WACCESS
    --- -------
      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
    964494 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points