Skip to Main Content
  • Questions
  • Load XML File (Physical file) using SQL Loader into a XMLType Column

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sreedhar.

Asked: August 17, 2017 - 5:56 am UTC

Last updated: October 12, 2017 - 2:37 pm UTC

Version: 10.1.0.5.0

Viewed 10K+ times! This question is

You Asked

Hello Tom

I need to load a XML File, which is available on the oracle server using a Host Program in Oracle Applications. I have done the below

1. Created a Host Concurrent Program
2. Call the SQL Loader Control File from the PROG File.

But the problem is the XML File data is loading multiple times into the table. Actually there are 67 rows in the XML and the XML file is loading 67 times. I want it to be loaded only once.

My Table

 CREATE TABLE XXCSI_IB_XML_FILES
    ( seq_no                       NUMBER                                 NOT NULL
     ,file_name                    VARCHAR2(4000)                         NOT NULL
     ,file_data                    XMLTYPE
     ,status                       VARCHAR2(25)         DEFAULT 'NEW'     NOT NULL
     ,sqlldr_request_id            NUMBER                                 NOT NULL
     ,created_by                   NUMBER      
     ,creation_date                DATE                 DEFAULT SYSDATE   NOT NULL
     ,last_updated_by              NUMBER      
     ,last_update_login     NUMBER
     ,last_update_date             DATE                 
     ,error_context                VARCHAR2(4000)    
     ,error_code                   VARCHAR2(500)
     ,error_message                VARCHAR2(4000)
     ,CONSTRAINT XXCSI_IB_XML_files_pk PRIMARY  KEY (seq_no)


My Control file looks like as

LOAD DATA
INFILE '/inbound/SR_XML_01.xml'
APPEND 
INTO TABLE XXCSI_IB_XML_FILES
   (SEQ_NO                              "IB_XML_FILES_S.NEXTVAL"
   ,FILE_NAME                CONSTANT   'SR_XML_01.xml'
   ,XML_FILE                 FILLER     CHAR(1000)
   ,FILE_DATA                LOBFILE(XML_FILE) TERMINATED BY EOF
   ,STATUS                   CONSTANT    'NEW'
   ,SQLLDR_REQUEST_ID                    "FND_GLOBAL.CONC_REQUEST_ID"
   ,CREATION_DATE                        "SYSDATE"
   ,CREATED_BY                           "FND_GLOBAL.USER_ID"
   ,LAST_UPDATE_DATE                     "SYSDATE"
   ,LAST_UPDATE_LOGIN                    "FND_GLOBAL.LOGIN_ID"
   ,LAST_UPDATED_BY                      "FND_GLOBAL.USER_ID"
   )


I see some error in the SQL Loader Log as well.

I have also tried a different control file version

LOAD DATA
INFILE '/inbound/SR_XML_01.xml'
APPEND 
INTO TABLE XXCSI_IB_XML_FILES
   (SEQ_NO                              "IB_XML_FILES_S.NEXTVAL"
   ,FILE_NAME                CONSTANT   'SR_XML_01.xml'
   ,FILE_DATA                LOBFILE(CONSTANT '/inbound/SR_XML_01.xml') TERMINATED BY EOF
   ,STATUS                   CONSTANT    'NEW'
   ,SQLLDR_REQUEST_ID                    "FND_GLOBAL.CONC_REQUEST_ID"
   ,CREATION_DATE                        "SYSDATE"
   ,CREATED_BY                           "FND_GLOBAL.USER_ID"
   ,LAST_UPDATE_DATE                     "SYSDATE"
   ,LAST_UPDATE_LOGIN                    "FND_GLOBAL.LOGIN_ID"
   ,LAST_UPDATED_BY                      "FND_GLOBAL.USER_ID"
   )


This is also loading the XML file multiple times.

My SQL Loader Version is
SQL*Loader: Release 10.1.0.5.0 - Production on Wed Aug 16 22:33:38 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Can you help me where I'm doing wrong ? Also if there is a different version which will load one record per file, please let me know.


Below is the sample XML i'm using to load
------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<transaction-data type="service_request" batch_transfer_id="41328" col_guid ="342223" transfer_start_time="2017-03-28 18:40:53.012">
<transactions>
 <service-request>
  <service_request_no>184</service_request_no>
    <service_request_type_cd>SEP Planned</service_request_type_cd>
    <priority_cd>Engineering time</priority_cd>
    <problem_cd>BQ Energy</problem_cd>
    <secondary_problem_cd>Autoshutter</secondary_problem_cd>
    <failure_dt>2017-03-25 16:38:00</failure_dt>
    <problem_summary_desc>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</problem_summary_desc>
    <resolution_desc>XXXXXXXXXXXXXXXXXXXXXXXXXXXX</resolution_desc>
  <task>
      <task_no>224</task_no>
      <new_task_fg>1</new_task_fg>
      <task_type_cd>Non-Scheduled Time</task_type_cd>
      <task_user_id>L123456</task_user_id>
      <task_subject_desc>XXXXXXXXXXXXXXX</task_subject_desc>
      <task_desc>XXXXXXXXXXX"</task_desc>
   <task-part>
        <part_no>X1234</part_no>
        <service_cd>INSTALL</service_cd>
        <part_serial_no>Y111111</part_serial_no>
        <shot_cnt>344</shot_cnt>
        <subinventory_cd>F-ALBANY</subinventory_cd>
        <deinstall_reason_cd>\N</deinstall_reason_cd>
        <replacement_trigger_cd>Low MO efficiency</replacement_trigger_cd>
        <module_cd>VACUUM_PUMP</module_cd>
        <part_replaced_fg>1</part_replaced_fg>
        <part_received_oracle_fg>1</part_received_oracle_fg>
        <shock_watch_tripped_fg>1</shock_watch_tripped_fg>
   </task-part>
   <task-part>
        <part_no>X2222</part_no>
        <service_cd>INSTALL</service_cd>
        <part_serial_no>Y111111</part_serial_no>
        <shot_cnt>344</shot_cnt>
        <subinventory_cd>F-ALBANY</subinventory_cd>
        <deinstall_reason_cd>\N</deinstall_reason_cd>
        <replacement_trigger_cd>Low MO efficiency</replacement_trigger_cd>
        <module_cd>VACUUM_PUMP</module_cd>
        <part_replaced_fg>0</part_replaced_fg>
        <part_received_oracle_fg>0</part_received_oracle_fg>
        <shock_watch_tripped_fg>0</shock_watch_tripped_fg>
   </task-part>
   <labor-activity>
        <labor_id>123</labor_id>
        <labor_service_activity_cd>Labor</labor_service_activity_cd>
        <labor_reason_cd>Customer Support</labor_reason_cd>
    <task_start_tm>2017-03-25 18:40:53.012</task_start_tm>
        <task_end_tm>2017-03-28 18:40:53.012</task_end_tm>
        <laser_downtime>3.5</laser_downtime>
        <labour_subcategory_cd>Customer Support</labour_subcategory_cd>
   </labor-activity>
   <labor-activity>
        <labor_id>134</labor_id>
        <labor_service_activity_cd>Travel</labor_service_activity_cd>
        <labor_reason_cd>Customer Support</labor_reason_cd>
        <task_start_tm>2017-03-25 18:40:53.012</task_start_tm>
        <task_end_tm>2017-03-28 18:40:53.012</task_end_tm>
        <laser_downtime>3.5</laser_downtime>
        <labour_subcategory_cd>Customer Support</labour_subcategory_cd>
   </labor-activity>
  </task>
 </service-request>
</transactions>
</transaction-data>


and Chris said...

Use the str clause of infile to specify the record terminator instead of in the lobfile clause.

Put the closing tag for the XML document here, for example:

LOAD DATA
INFILE 'SR.xml' "str '</transaction-data>'"
INTO TABLE XXCSI_IB_XML_FILES
TRUNCATE
   (SEQ_NO                              "IB_XML_FILES_S.NEXTVAL"
   ,FILE_NAME                CONSTANT   'SR_XML_01.xml'
   ,FILE_DATA  lobfile(CONSTANT 'SR.xml') 
   ,STATUS                   CONSTANT    'NEW'
   ,SQLLDR_REQUEST_ID                    "1"
   ,CREATION_DATE                        "SYSDATE"
   ,CREATED_BY                           "1"
   ,LAST_UPDATE_DATE                     "SYSDATE"
   ,LAST_UPDATE_LOGIN                    "1"
   ,LAST_UPDATED_BY                      "1"
   )

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sreedhar Reddy, August 21, 2017 - 9:54 pm UTC

I have changed my control file as below. I have used the STR clause in the INFILE and also removed the TERMINATED BY EOF at the LOGFILE Clause. This time there are 2 records loaded but without the XML data. Meaning FILE DATA field is EMPTY.

Please see if I'm doing anything wrong

LOAD DATA
INFILE '/inbound/SR_XML_01.xml' "str '</transaction-data>'"
APPEND
INTO TABLE XXCSI_IB_XML_FILES
(SEQ_NO "CYCSI_DUNKEL_IB_XML_FILES_S.NEXTVAL"
,FILE_NAME CONSTANT 'SR_XML_01.xml'
,FILE_DATA LOBFILE(CONSTANT '/inbound/SR_XML_01.xml')
,STATUS CONSTANT 'NEW'
,SQLLDR_REQUEST_ID "FND_GLOBAL.CONC_REQUEST_ID"
,CREATION_DATE "SYSDATE"
,CREATED_BY "FND_GLOBAL.USER_ID"
,LAST_UPDATE_DATE "SYSDATE"
,LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
)


Control file Log
----------------

SQL*Loader: Release 10.1.0.5.0 - Production on Mon Aug 21 14:40:07 2017

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: /bin/SR_XML_01.xml.clt
Data File: /staging/SR_XML_01.xml
File processing option string: "str '</transaction-data>'"
Bad File: /logs/appl/conc/out/o722171978.bad
Discard File: /logs/appl/conc/out/o722171978.dis
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table "XXADMIN"."IB_XML_FILES", loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SEQ_NO FIRST 1 CHARACTER
SQL string for column : "IB_XML_FILES_S.NEXTVAL"
FILE_NAME CONSTANT
Value is 'SR_XML_01.xml'
FILE_DATA DERIVED 1 CHARACTER
Static LOBFILE. Filename is /staging/SR_XML_01.xml
STATUS CONSTANT
Value is 'NEW'
SQLLDR_REQUEST_ID NEXT 1 CHARACTER
SQL string for column : "FND_GLOBAL.CONC_REQUEST_ID"
CREATION_DATE NEXT 1 CHARACTER
SQL string for column : "SYSDATE"
CREATED_BY NEXT 1 CHARACTER
SQL string for column : "FND_GLOBAL.USER_ID"
LAST_UPDATE_DATE NEXT 1 CHARACTER
SQL string for column : "SYSDATE"
LAST_UPDATE_LOGIN NEXT 1 CHARACTER
SQL string for column : "FND_GLOBAL.LOGIN_ID"
LAST_UPDATED_BY NEXT 1 CHARACTER
SQL string for column : "FND_GLOBAL.USER_ID"


Table "XXADMIN"."IB_XML_FILES":
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 3904 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Mon Aug 21 14:40:07 2017
Run ended on Mon Aug 21 14:40:09 2017

Elapsed time was: 00:00:02.33
Chris Saxon
August 22, 2017 - 1:40 pm UTC

What are the exact contents of the file /inbound/SR_XML_01.xml? The XML you posted or something else?

Sreedhar Reddy, August 21, 2017 - 9:56 pm UTC

I have changed my control file as below. I have used the STR clause in the INFILE and also removed the TERMINATED BY EOF at the LOGFILE Clause. This time there are 2 records loaded but without the XML data. Meaning FILE DATA field is EMPTY.

Please see if I'm doing anything wrong

LOAD DATA
INFILE '/inbound/SR_XML_01.xml' "str '</transaction-data>'"
APPEND
INTO TABLE XXCSI_IB_XML_FILES
(SEQ_NO "CYCSI_DUNKEL_IB_XML_FILES_S.NEXTVAL"
,FILE_NAME CONSTANT 'SR_XML_01.xml'
,FILE_DATA LOBFILE(CONSTANT '/inbound/SR_XML_01.xml')
,STATUS CONSTANT 'NEW'
,SQLLDR_REQUEST_ID "FND_GLOBAL.CONC_REQUEST_ID"
,CREATION_DATE "SYSDATE"
,CREATED_BY "FND_GLOBAL.USER_ID"
,LAST_UPDATE_DATE "SYSDATE"
,LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
)
Chris Saxon
October 12, 2017 - 2:37 pm UTC

You don't need the lobfile clause after file_data. This is telling SQL Loader to look in a different file to the one you specify in INFILE. Sorry for the confusion.

You can overcome this by loading data from the control file itself with *. Then use the TERMINATED BY EOF clause as you tried originally:

LOAD DATA
INFILE *
INTO TABLE XXCSI_IB_XML_FILES
APPEND
TRAILING NULLCOLS
   (SEQ_NO                              "IB_XML_FILES_S.NEXTVAL"
   ,FILE_NAME                CONSTANT   'SR_XML_01.xml'
   ,FILE_DATA  LOBFILE( CONSTANT 'SR.xml') CHAR TERMINATED BY EOF
   ,STATUS                   CONSTANT    'NEW'
   ,SQLLDR_REQUEST_ID                    "1"
   ,CREATION_DATE                        "SYSDATE"
   ,CREATED_BY                           "1"
   ,LAST_UPDATE_DATE                     "SYSDATE"
   ,LAST_UPDATE_LOGIN                    "1"
   ,LAST_UPDATED_BY                      "1"
   )
BEGINDATA
"SR.xml"


This will also allow you to load several XML files in one go if you wish ;)

LOAD DATA
INFILE *
INTO TABLE XXCSI_IB_XML_FILES
APPEND
TRAILING NULLCOLS
   (SEQ_NO                              "IB_XML_FILES_S.NEXTVAL"
   ,FILE_NAME          CHAR(100) ENCLOSED BY '"'
   ,FILE_DATA  LOBFILE( FILE_NAME ) CHAR TERMINATED BY EOF
   ,STATUS                   CONSTANT    'NEW'
   ,SQLLDR_REQUEST_ID                    "1"
   ,CREATION_DATE                        "SYSDATE"
   ,CREATED_BY                           "1"
   ,LAST_UPDATE_DATE                     "SYSDATE"
   ,LAST_UPDATE_LOGIN                    "1"
   ,LAST_UPDATED_BY                      "1"
   )
BEGINDATA
 "SR.xml"
 "SR2.xml"

Sreedhar Reddy, August 22, 2017 - 3:07 pm UTC

It is exactly the same data in the XML file. Here is it again. I see an extra empty line at the very end of the file. As I copied, you don't able to see it.


<?xml version="1.0" encoding="UTF-8"?>
<transaction-data type="service_request" batch_transfer_id="41328" col_guid ="342223" transfer_start_time="2017-03-28 18:40:53.012">
<transactions>
<service-request>
<service_request_no>184</service_request_no>
<service_request_type_cd>SEP Planned</service_request_type_cd>
<priority_cd>Engineering time</priority_cd>
<problem_cd>BQ Energy</problem_cd>
<secondary_problem_cd>Autoshutter</secondary_problem_cd>
<failure_dt>2017-03-25 16:38:00</failure_dt>
<problem_summary_desc>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</problem_summary_desc>
<resolution_desc>XXXXXXXXXXXXXXXXXXXXXXXXXXXX</resolution_desc>
<task>
<task_no>224</task_no>
<new_task_fg>1</new_task_fg>
<task_type_cd>Non-Scheduled Time</task_type_cd>
<task_user_id>L123456</task_user_id>
<task_subject_desc>XXXXXXXXXXXXXXX</task_subject_desc>
<task_desc>XXXXXXXXXXX"</task_desc>
<task-part>
<part_no>X1234</part_no>
<service_cd>INSTALL</service_cd>
<part_serial_no>Y111111</part_serial_no>
<shot_cnt>344</shot_cnt>
<subinventory_cd>F-ALBANY</subinventory_cd>
<deinstall_reason_cd>\N</deinstall_reason_cd>
<replacement_trigger_cd>Low MO efficiency</replacement_trigger_cd>
<module_cd>VACUUM_PUMP</module_cd>
<part_replaced_fg>1</part_replaced_fg>
<part_received_oracle_fg>1</part_received_oracle_fg>
<shock_watch_tripped_fg>1</shock_watch_tripped_fg>
</task-part>
<task-part>
<part_no>X2222</part_no>
<service_cd>INSTALL</service_cd>
<part_serial_no>Y111111</part_serial_no>
<shot_cnt>344</shot_cnt>
<subinventory_cd>F-ALBANY</subinventory_cd>
<deinstall_reason_cd>\N</deinstall_reason_cd>
<replacement_trigger_cd>Low MO efficiency</replacement_trigger_cd>
<module_cd>VACUUM_PUMP</module_cd>
<part_replaced_fg>0</part_replaced_fg>
<part_received_oracle_fg>0</part_received_oracle_fg>
<shock_watch_tripped_fg>0</shock_watch_tripped_fg>
</task-part>
<labor-activity>
<labor_id>123</labor_id>
<labor_service_activity_cd>Labor</labor_service_activity_cd>
<labor_reason_cd>Customer Support</labor_reason_cd>
<task_start_tm>2017-03-25 18:40:53.012</task_start_tm>
<task_end_tm>2017-03-28 18:40:53.012</task_end_tm>
<laser_downtime>3.5</laser_downtime>
<labour_subcategory_cd>Customer Support</labour_subcategory_cd>
</labor-activity>
<labor-activity>
<labor_id>134</labor_id>
<labor_service_activity_cd>Travel</labor_service_activity_cd>
<labor_reason_cd>Customer Support</labor_reason_cd>
<task_start_tm>2017-03-25 18:40:53.012</task_start_tm>
<task_end_tm>2017-03-28 18:40:53.012</task_end_tm>
<laser_downtime>3.5</laser_downtime>
<labour_subcategory_cd>Customer Support</labour_subcategory_cd>
</labor-activity>
</task>
</service-request>
</transactions>
</transaction-data>

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.