4 Replies Latest reply on Jan 12, 2016 5:11 PM by Bill Herrin

    How to load and ASCII XML data file into an Oracle table with SQLLDR

    Bill Herrin
      • I have been given an ASCII flat file that contains XML data.  MS Excel was able to decode the structure and load it into a spreadsheet.  I have created a table in Oracle with matching columns, and I am trying to use SQLLDR to load the ASCII file into the Oracle table.  I cloned a control file example, but that is not working for me.  What documentation or control file examples would show how to do this?  Is there a different Oracle tool that is better suited to this type of load?  Thanks.
        • 1. Re: How to load and ASCII XML data file into an Oracle table with SQLLDR
          manakriti

          Hi Bill,

           

          What error exactly you are getting while processing this file using SQl Loader, Mean while one can load excel file in to oracle using sql loader for that you need to convert this file in to a CSV file or a delimited file.

           

          load data

          infile 'file name with location'

          into table Table_name

          fields terminated by "delimeter" optionally enclosed by '"'         

          ( col1,col2,col3..)

           

          Possible problem can come when excel file cells contains new line character (ALT+ENTER) in this case you need to remove this new line character from cells.

          1 person found this helpful
          • 2. Re: How to load and ASCII XML data file into an Oracle table with SQLLDR
            michael.sakayeda-Oracle

            Hi Bill,

             

            1. You could load the data into a table with an XML column:

             

            SQL > create table xmltab (xml_data xmltype);

             

            Then load it with this SQL*Loader control file:

             

            LOAD DATA

            INFILE *

            INTO TABLE xmltab

            (

              file_name filler char(100),

              xml_data LOBFILE (file_name) TERMINATED BY EOF

            )

            BEGINDATA

            dept.xml

             

            Then use the Oracle XML SQL functions to retrieve the data (e.g. "EXTRACTVALUE").

             

            2. You can also load the xml document into a table with a CLOB column (instead of an XMLTYPE column) and again use XML SQL functions.

            Note that you will need to cast the clob column to a XMLTYPE to pass as an argument to the XML functions.

             

            3. You can also use SQL*Loader and specify the tags in the control file:

             

            Here's the xml file:

             

            $ cat dept.xml

            <DEPT>

            <ROW>

               <DEPTNO>12</DEPTNO>

               <DNAME>RESEARCH</DNAME>

               <LOC>SARATOGA</LOC>

            </ROW>

            <ROW>

               <DEPTNO>10</DEPTNO>

               <DNAME>ACCOUNTING</DNAME>

               <LOC>CLEVELAND</LOC>

            </ROW>

            </DEPT>

             

            Here's the SQL*Loader control file:

             

            $ cat dept.ctl

            LOAD DATA

            INFILE 'dept.xml'

            CONCATENATE 5

            INTO TABLE dept

            (

                 dummy FILLER CHAR(13) TERMINATED BY "<ROW>",

                 deptno CHAR(30) ENCLOSED BY "<DEPTNO>" AND "</DEPTNO>",

                 dname CHAR(30) ENCLOSED BY "<DNAME>" AND "</DNAME>",

                 loc CHAR(30) ENCLOSED BY "<LOC>" AND "</LOC>"

            )

             

            4. Lastly, you can use external tables:

             

            SQL>

            SQL> host cat /tmp/dept.xml

            <DEPT>

              <ROW>

              <DEPTNO>12</DEPTNO>

              <DNAME>RESEARCH</DNAME>

              <LOC>SARATOGA</LOC>

              </ROW>

              <ROW>

              <DEPTNO>10</DEPTNO>

              <DNAME>ACCOUNTING</DNAME>

              <LOC>CLEVELAND</LOC>

              </ROW>

            </DEPT>

             

            SQL>

            SQL> connect /as sysdba

            Connected.

            SQL>

            SQL> create or replace directory d as '/tmp';

             

            Directory created.

             

            SQL>

            SQL> grant all on directory d to scott;

             

            Grant succeeded.

             

            SQL>

            SQL> connect scott/tiger

            Connected.

            SQL>

            SQL>

            SQL> CREATE TABLE deptxml (

              2 deptno NUMBER,

              3 dname VARCHAR2(14),

              4 loc VARCHAR2(13)

              5 )

              6 ORGANIZATION EXTERNAL

              7 (

              8 TYPE ORACLE_LOADER

              9 DEFAULT DIRECTORY d

            10 ACCESS PARAMETERS

            11 (

            12 RECORDS DELIMITED BY "</ROW>"

            13 FIELDS

            14 (

            15 dummy CHAR(15) TERMINATED BY "<ROW>",

            16 deptno CHAR(2) ENCLOSED BY "<DEPTNO>" AND "</DEPTNO>",

            17 dname CHAR(14) ENCLOSED BY "<DNAME>" AND "</DNAME>",

            18 loc CHAR(13) ENCLOSED BY "<LOC>" AND "</LOC>"

            19 )

            20 )

            21 LOCATION ('dept.xml')

            22 )

            23 PARALLEL

            24 REJECT LIMIT UNLIMITED;

             

            Table created.

             

            SQL>

            SQL> select * from deptxml;

             

              DEPTNO DNAME LOC 

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

              12 RESEARCH SARATOGA 

              10 ACCOUNTING CLEVELAND 

             

            2 rows selected.

             

            SQL>

             

            Note that with the first 2 cases you will load the XML document into a staging table and then insert into the destination from this staging table.

            For the external table case you could use insert-as-select to insert from the external table into the destination table.

            1 person found this helpful
            • 3. Re: How to load and ASCII XML data file into an Oracle table with SQLLDR
              Barbara Boehmer

              SQL*Loader can load files from the client.  External tables can only access the server.  If the file is on the server, another method is to use bfilename and xmltable to access the files directly, from SQL as demonstrated below.  You can create table as select as I did below or you can insert into an existing table using select with xmltable and bfilename.

               

              SCOTT@orcl> HOST TYPE c:\my_oracle_files\dept.xml

              <DEPT>

              <ROW>

              <DEPTNO>12</DEPTNO>

              <DNAME>RESEARCH</DNAME>

              <LOC>SARATOGA</LOC>

              </ROW>

              <ROW>

              <DEPTNO>10</DEPTNO>

              <DNAME>ACCOUNTING</DNAME>

              <LOC>CLEVELAND</LOC>

              </ROW>

              </DEPT>

               

              SCOTT@orcl> CREATE OR REPLACE DIRECTORY D AS 'c:\my_oracle_files'

                2  /

               

              Directory created.

               

              SCOTT@orcl> CREATE TABLE deptxml AS

                2  SELECT *

                3  FROM   XMLTABLE

                4           ('/DEPT/ROW'

                5            PASSING XMLTYPE (BFILENAME ('D', 'dept.xml'), NLS_CHARSET_ID ('AL32UTF8'))

                6            COLUMNS

                7              deptno    NUMBER        PATH 'DEPTNO',

                8              dname     VARCHAR2(14)  PATH 'DNAME',

                9              loc       VARCHAR2(13)  PATH 'LOC')

              10  /

               

              Table created.

               

              SCOTT@orcl> SELECT * FROM deptxml

                2  /

               

                  DEPTNO DNAME          LOC

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

                      12 RESEARCH       SARATOGA

                      10 ACCOUNTING     CLEVELAND

               

              2 rows selected.

              1 person found this helpful
              • 4. Re: How to load and ASCII XML data file into an Oracle table with SQLLDR
                Bill Herrin

                                        

                Thanks Mankrit.  This worked well for an interim solution.  I will proceed to build a control file per some of the other suggestions.