1 Reply Latest reply: Dec 8, 2011 12:53 AM by AlexAnd RSS

    Process multiple XML files in directory

    880561
      Hi,

      I have used the below forum thead to successfully load the XML data into my tables:

      Load xml data in Oracle table

      i have used the following to identify the xml file in the TEST_DIR:

      acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );

      My requirement is pass the file name (.xml) dynamically. In the sense i have to process all the files in the directory - TEST_DIR. how can i achieve this.

      Could you guys help me in this regard.

      Thanks in advance.

      Mr. KK
        • 1. Re: Process multiple XML files in directory
          AlexAnd
          first, you must read directory so see
          http://birijan.com.np/?q=plsq+directory+listing
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584
          List files in oracle directory
          SQL> conn sys@ora as sysdba
          Enter password: **********
          Connected.
          
          SQL>  create or replace type rowFile as object (fnum number, fname varchar2 (100))
            2  /
          
          Type created.
          
          SQL> create or replace type rowFiles as table of rowFile
            2  /
          
          Type created.
          
          SQL> create or replace function get_list_files (directory varchar2)
            2   RETURN rowFiles PIPELINED
            3   IS
            4       ns          VARCHAR2(1024);
            5       v_directory varchar2(1024);
            6       i number := 0;
            7   BEGIN
            8        v_directory := directory;
            9        SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(v_directory, ns);
           10   FOR each_file IN (SELECT fname_krbmsft AS name FROM x$krbmsft) LOOP
           11       dbms_output.put_line(each_file.name);
           12       i := i + 1;
           13       PIPE ROW(rowFile(i,each_file.name)); 
           14   end loop;
           15   END;
           16  /
          
          Function created.
          
          SQL> grant execute on get_list_files to scott
            2  /
          
          Grant succeeded.
          
          SQL> conn scott@ora
          Enter password: *****
          Connected.
          SQL>  select fnum, fname, substr(fname,instr(fname,'\',-1)+1) 
            2   from table(sys.get_list_files('C:\tmp\get_xml_dir'))
            3   where upper(fname) like '%.XML'
            4  /
          
                FNUM
          ----------
          FNAME
          --------------------------------------------------------------------------------
          SUBSTR(FNAME,INSTR(FNAME,'\',-1)+1)
          --------------------------------------------------------------------------------
                   1
          C:\TMP\get_xml_dir\t.xml
          t.xml
          
                   2
          C:\TMP\get_xml_dir\u.xml
          u.xml
          
                FNUM
          ----------
          FNAME
          --------------------------------------------------------------------------------
          SUBSTR(FNAME,INSTR(FNAME,'\',-1)+1)
          --------------------------------------------------------------------------------
          
          
          SQL> 
          for use
          xmltype( bfilename('DIR','x.xml'), nls_charset_id('AL32UTF8') );
          in my case i have
          SQL> select directory_name, directory_path from dba_directories where directory_name = 'GET_XML'
            2  /
          
          DIRECTORY_NAME
          ------------------------------
          DIRECTORY_PATH
          --------------------------------------------------------------------------------
          GET_XML
          c:\tmp\get_xml_dir
          
          
          SQL> 
          for me the path for reading directory's file and the oracle directory are SAME!

          so
          SQL> create table get_xml (x xmltype)
            2  /
          
          Table created.
          
          SQL> select count(*) from get_xml
            2  /
          
            COUNT(*)
          ----------
                   0
          
          SQL> 
          and
          SQL> begin
            2   for x in ( select substr(fname,instr(fname,'\',-1)+1)  nm
            3               from table(sys.get_list_files('C:\tmp\get_xml_dir'))
            4               where upper(fname) like '%.XML')
            5   loop
            6     insert into get_xml values(xmltype( bfilename('GET_XML',x.nm), nls_charset_id('AL32UTF8') ))
          ;
            7  end loop;
            8  end;
            9  /
          
          PL/SQL procedure successfully completed.
          
          SQL> select count(*) from get_xml
            2  /
          
            COUNT(*)
          ----------
                   2
          
          SQL>