2 Replies Latest reply: Jul 17, 2014 3:45 PM by Barbara Boehmer RSS

    import XML file into Oracle table using external table or any other method

    Ashu_Neo

      Hi All,
      I am receiving .XML file in unix server directory and i want to import the data from .xml file into my oracle table using external table OR any method to import XML file will be helpful.

      I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.

       

      Thanks

      Ashutosh

        • 1. Re: import XML file into Oracle table using external table or any other method
          Dan Jankowski

          Here's some notes I dug up on loading multiple XML files with SQL Loader. You can probably simplify this to load a single file, and I'm sure it could be adapted to use external tables as well.

           

          Create a file (file_list.txt) containing a list of all the files to be imported, with their full path. This can be produced from a directory listing, e.g.

           

          dir "C:\data\" /B/S > file_list.txt

           

          Create a SQL*Loader control file for the load (called load.ctl). The filename can be passed into the table as a field (useful for tracking back to individual files), using the following CTL file. (Assumes you have the correct table created).

           

          load data

          infile 'file_list.txt'

          into table t_xml

          ( filename char(1000),

            xml_content lobfile(filename) terminated by EOF)

           

          If the filename is not required, use FILLER, ie.

           

          load data

          infile 'file_list.txt'

          into table t_xml

          ( filename FILLER char(1000),

            xml_content lobfile(filename) terminated by EOF)

           

          Run SQL*Loader as follows

           

          sqlldr user/password@instance control=load.ctl log=load.log bad=load.bad

           

          If desired, records can be assigned a unique ID after import (there's probably a way of doing this during import which I haven't figured out yet)

           

          UPDATE t_xml

          SET file_id = seq_file_id.nextval

          • 2. Re: import XML file into Oracle table using external table or any other method
            Barbara Boehmer

            I have demonstrated three methods below, beginning with what was suggested by Dan Jankowski.  I used a database sequence, but you could also use a SQL*Loader sequence.  Also, the last method can be simplified if there is only one file.

             

            SCOTT@orcl> -- If you have the following files and contents:

            SCOTT@orcl> host type c:\my_oracle_files\file_list.txt

            file1.xml

            file2.xml

             

            SCOTT@orcl> host type c:\my_oracle_files\file1.xml

            <?xml version="1.0"?>

            <ROWSET>

            <ROW>

              <DEPTNO>10</DEPTNO>

              <DNAME>ACCOUNTING</DNAME>

            </ROW>

            <ROW>

              <DEPTNO>20</DEPTNO>

              <DNAME>RESEARCH</DNAME>

            </ROW>

            </ROWSET>

             

            SCOTT@orcl> host type c:\my_oracle_files\file2.xml

            <?xml version="1.0"?>

            <ROWSET>

            <ROW>

              <EMPNO>7369</EMPNO>

              <ENAME>SMITH</ENAME>

              <DEPTNO>20</DEPTNO>

            </ROW>

            <ROW>

              <EMPNO>7566</EMPNO>

              <ENAME>JONES</ENAME>

              <DEPTNO>20</DEPTNO>

            </ROW>

            <ROW>

              <EMPNO>7782</EMPNO>

              <ENAME>CLARK</ENAME>

              <DEPTNO>10</DEPTNO>

            </ROW>

            <ROW>

              <EMPNO>7788</EMPNO>

              <ENAME>SCOTT</ENAME>

              <DEPTNO>20</DEPTNO>

            </ROW>

            <ROW>

              <EMPNO>7839</EMPNO>

              <ENAME>KING</ENAME>

              <DEPTNO>10</DEPTNO>

            </ROW>

            <ROW>

              <EMPNO>7876</EMPNO>

              <ENAME>ADAMS</ENAME>

              <DEPTNO>20</DEPTNO>

            </ROW>

            <ROW>

              <EMPNO>7902</EMPNO>

              <ENAME>FORD</ENAME>

              <DEPTNO>20</DEPTNO>

            </ROW>

            <ROW>

              <EMPNO>7934</EMPNO>

              <ENAME>MILLER</ENAME>

              <DEPTNO>10</DEPTNO>

            </ROW>

            </ROWSET>

             

            SCOTT@orcl> -- and you have a table like the following to load the data into:

            SCOTT@orcl> create table t_xml

              2    (seq         number,

              3      filename     varchar2(30),

              4      xml_content  xmltype)

              5  /

             

            Table created.

             

            SCOTT@orcl> -- and you have a sequence:

            SCOTT@orcl> create sequence seq_file_id

              2  /

             

            Sequence created.

             

            SCOTT@orcl> -- then you can use either of the three following methods to load the data:

            SCOTT@orcl> -- 1.  Load the data using SQL*Loader (as suggested by Dan Jankowski):

            SCOTT@orcl> truncate table t_xml

              2  /

             

            Table truncated.

             

            SCOTT@orcl> host type c:\my_oracle_files\load.ctl

            load data

            infile 'file_list.txt'

            append

            into table t_xml

            ( filename char(50000)

            , seq  "seq_file_id.nextval"

            , xml_content lobfile(filename) terminated by EOF)

             

            SCOTT@orcl> host sqlldr scott/tiger@orcl control=load.ctl log=load.log bad=load.bad

             

            SCOTT@orcl> column xml_content format a30

            SCOTT@orcl> select * from t_xml

              2  /

             

                   SEQ FILENAME                       XML_CONTENT

            ---------- ------------------------------ ------------------------------

                     1 file1.xml                      <?xml version="1.0"?>

                                                      <ROWSET>

                                                        <ROW>

                                                          <DEPTNO>10</DEPTNO>

                                                          <DNAME>ACCOUNTING</DNAME>

                                                        </ROW>

                                                        <ROW>

                                                          <DEPTNO>20</DEPTNO>

                                                          <DNAME>RESEARCH</DNAME>

                                                        </ROW>

                                                      </ROWSET>

             

                     2 file2.xml                      <?xml version="1.0"?>

                                                      <ROWSET>

                                                        <ROW>

                                                          <EMPNO>7369</EMPNO>

                                                          <ENAME>SMITH</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7566</EMPNO>

                                                          <ENAME>JONES</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7782</EMPNO>

                                                          <ENAME>CLARK</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7788</EMPNO>

                                                          <ENAME>SCOTT</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7839</EMPNO>

                                                          <ENAME>KING</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7876</EMPNO>

                                                          <ENAME>ADAMS</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7902</EMPNO>

                                                          <ENAME>FORD</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7934</EMPNO>

                                                          <ENAME>MILLER</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                      </ROWSET>

             

             

            2 rows selected.

             

            SCOTT@orcl> -- 2.  Access the data using an external table (files must be on server, not client):

            SCOTT@orcl> truncate table t_xml

              2  /

             

            Table truncated.

             

            SCOTT@orcl> create or replace directory my_dir as 'c:\my_oracle_files'

              2  /

             

            Directory created.

             

            SCOTT@orcl> create table external_table

              2    (filename     varchar2(30),

              3      xml_content  clob)

              4  organization external

              5    (type oracle_loader

              6      default directory my_dir

              7      access parameters

              8        (records delimited by newline

              9         badfile 'load.bad'

            10         logfile 'load.log'

            11         fields terminated by ','

            12         missing field values are null

            13           (filename    char(50000))

            14         column transforms

            15           (xml_content from lobfile (filename) from (my_dir) clob))

            16    location ('file_list.txt'))

            17  reject limit unlimited

            18  /

             

            Table created.

             

            SCOTT@orcl> insert into t_xml (seq, filename, xml_content)

              2  select seq_file_id.nextval, filename, xmltype (xml_content)

              3  from   external_table

              4  /

             

            2 rows created.

             

            SCOTT@orcl> column xml_content format a30

            SCOTT@orcl> select * from t_xml

              2  /

             

                   SEQ FILENAME                       XML_CONTENT

            ---------- ------------------------------ ------------------------------

                     3 file1.xml                      <?xml version="1.0"?>

                                                      <ROWSET>

                                                        <ROW>

                                                          <DEPTNO>10</DEPTNO>

                                                          <DNAME>ACCOUNTING</DNAME>

                                                        </ROW>

                                                        <ROW>

                                                          <DEPTNO>20</DEPTNO>

                                                          <DNAME>RESEARCH</DNAME>

                                                        </ROW>

                                                      </ROWSET>

             

                     4 file2.xml                      <?xml version="1.0"?>

                                                      <ROWSET>

                                                        <ROW>

                                                          <EMPNO>7369</EMPNO>

                                                          <ENAME>SMITH</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7566</EMPNO>

                                                          <ENAME>JONES</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7782</EMPNO>

                                                          <ENAME>CLARK</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7788</EMPNO>

                                                          <ENAME>SCOTT</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7839</EMPNO>

                                                          <ENAME>KING</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7876</EMPNO>

                                                          <ENAME>ADAMS</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7902</EMPNO>

                                                          <ENAME>FORD</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7934</EMPNO>

                                                          <ENAME>MILLER</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                      </ROWSET>

             

             

            2 rows selected.

             

            SCOTT@orcl> -- 3.  Access the data directly using bfilename (files must be on server, not client)

            SCOTT@orcl> --       accessing file list through externa table:

            SCOTT@orcl> truncate table t_xml

              2  /

             

            Table truncated.

             

            SCOTT@orcl> create or replace directory my_dir as 'c:\my_oracle_files'

              2  /

             

            Directory created.

             

            SCOTT@orcl> create table file_list

              2    (filename     varchar2(30))

              3  organization external

              4    (type oracle_loader

              5      default directory my_dir

              6      access parameters

              7        (records delimited by newline

              8         nobadfile

              9         nologfile

            10         fields terminated by ','

            11         missing field values are null

            12           (filename    char(50000)))

            13    location ('file_list.txt'))

            14  reject limit unlimited

            15  /

             

            Table created.

             

            SCOTT@orcl> select * from file_list

              2  /

             

            FILENAME

            ------------------------------

            file1.xml

            file2.xml

             

            2 rows selected.

             

            SCOTT@orcl> insert into t_xml (seq, filename, xml_content)

              2  select seq_file_id.nextval, t.filename, x.column_value

              3  from   file_list t,

              4          table

              5            (xmlsequence

              6           (xmltype

              7              (bfilename ('MY_DIR', t.filename),

              8               nls_charset_id ('WE8MSWIN1252')))) x

              9  /

             

            2 rows created.

             

            SCOTT@orcl> column xml_content format a30

            SCOTT@orcl> select * from t_xml

              2  /

             

                   SEQ FILENAME                       XML_CONTENT

            ---------- ------------------------------ ------------------------------

                     5 file1.xml                      <?xml version="1.0"?>

                                                      <ROWSET>

                                                        <ROW>

                                                          <DEPTNO>10</DEPTNO>

                                                          <DNAME>ACCOUNTING</DNAME>

                                                        </ROW>

                                                        <ROW>

                                                          <DEPTNO>20</DEPTNO>

                                                          <DNAME>RESEARCH</DNAME>

                                                        </ROW>

                                                      </ROWSET>

             

                     6 file2.xml                      <?xml version="1.0"?>

                                                      <ROWSET>

                                                        <ROW>

                                                          <EMPNO>7369</EMPNO>

                                                          <ENAME>SMITH</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7566</EMPNO>

                                                          <ENAME>JONES</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7782</EMPNO>

                                                          <ENAME>CLARK</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7788</EMPNO>

                                                          <ENAME>SCOTT</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7839</EMPNO>

                                                          <ENAME>KING</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7876</EMPNO>

                                                          <ENAME>ADAMS</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7902</EMPNO>

                                                          <ENAME>FORD</ENAME>

                                                          <DEPTNO>20</DEPTNO>

                                                        </ROW>

                                                        <ROW>

                                                          <EMPNO>7934</EMPNO>

                                                          <ENAME>MILLER</ENAME>

                                                          <DEPTNO>10</DEPTNO>

                                                        </ROW>

                                                      </ROWSET>

             

             

            2 rows selected.