| SQL Loader for XML File [message #674536] |
Thu, 31 January 2019 14:46  |
 |
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
|
Member |
|
|
HI All,
I just want to load a XML file into a table which we recieve on daily basis.he File_ID value should increment everytime a new value comes and File Name should be populated from File as the File name will have date stamp attached to it.
OPTIONS ( skip=1)
LOAD DATA
infile 'C:\Users\HVentrap\Desktop\INVCTL\STATUS*.xml'
INTO TABLE PQINF.XXPQ_EDI_INV_STS_FILE
(FILE_ID "XXPQ_EDI_INV_STS_F.NEXTVAL"
,FILE_NAME
,DELIVERY_FILE
,PROCESS_FLAG "N"
,CREATION_DATE "SYSDATE"
,CREATED_BY "FND_GLOBAL.USER_ID"
,LAST_UPDATE_DATE "SYSDATE"
,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
)
CREATE TABLE XXPQ_EDI_INV_STS_FILE
(
FILE_ID NUMBER,
FILE_NAME VARCHAR2(200 BYTE),
DELIVERY_FILE SYS.XMLTYPE,
PROCESS_FLAG VARCHAR2(1 BYTE),
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER
)
Attached sample XML File for reference.PLease suggest how Can I do this
|
|
|
|
|
|
|
|
| Re: SQL Loader for XML File [message #674553 is a reply to message #674536] |
Fri, 01 February 2019 05:58  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I am no expert with SQL*Loader, but even I can see so many mistakes in your controlfile. You are not even telling it the format of the infile.
Some more really obvious ones:
infile 'C:\Users\HVentrap\Desktop\INVCTL\STATUS*.xml'
You can't use an asterisk wildcard in release 11
,CREATION_DATE "SYSDATE"
You can't put the word SYSDATE in the column CREATION_DATE, those double quotes have to go.
|
|
|
|
|
|
| Re: SQL Loader for XML File [message #674898 is a reply to message #674569] |
Thu, 21 February 2019 06:08 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your xml file names need to either be listed at the end of the control file after BEGINDATA, as in the example below or listed in a separate data file and that data file name would either need to be after INFILE in the control file or in the SQL*Loader command line after DATA=. Either way, the names of the xml files would be obtained via the file_name, making sure you indicate some sort of delimiter and set the position appropriately. The xml file names obtained via the file_name would then be reference in the lobfile. You should not be attempting to use the xml file names as literal values in the field descriptions of file_name or lobfile. Please see the example below. I have removed the schema names and functions in the example below, since I do not have your schemas or functions.
-- xml files for testing:
SCOTT@orcl_12.1.0.2.0> HOST TYPE PROQUEST_STATUS_12_12_2018.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_12.1.0.2.0> HOST TYPE PROQUEST_STATUS_12_13_2018.xml
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
</ROW>
</ROWSET>
-- SQL*Loader control file:
SCOTT@orcl_12.1.0.2.0> HOST TYPE test.ctl
LOAD DATA
INFILE *
APPEND INTO TABLE XXPQ_EDI_INV_STS_FILE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(FILE_ID "EDI_STS_FILES_S.NEXTVAL"
,FILE_NAME POSITION(1)
,DELIVERY_FILE LOBFILE(file_name) TERMINATED BY EOF
,PROCESS_FLAG CONSTANT 'N'
,CREATION_DATE "SYSDATE"
,LAST_UPDATE_DATE "SYSDATE"
)
BEGINDATA
PROQUEST_STATUS_12_12_2018.xml
PROQUEST_STATUS_12_13_2018.xml
-- table:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE XXPQ_EDI_INV_STS_FILE
2 (
3 FILE_ID NUMBER,
4 FILE_NAME VARCHAR2(200 BYTE),
5 DELIVERY_FILE SYS.XMLTYPE,
6 PROCESS_FLAG VARCHAR2(1 BYTE),
7 CREATION_DATE DATE,
8 CREATED_BY NUMBER,
9 LAST_UPDATE_DATE DATE,
10 LAST_UPDATED_BY NUMBER
11 )
12 /
Table created.
-- sequence:
SCOTT@orcl_12.1.0.2.0> CREATE SEQUENCE EDI_STS_FILES_S
2 /
Sequence created.
-- load:
SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Feb 21 03:55:28 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 2
Table XXPQ_EDI_INV_STS_FILE:
2 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
-- results:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM XXPQ_EDI_INV_STS_FILE
2 /
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
DELIVERY_FILE
--------------------------------------------------------------------------------
P CREATION_DATE CREATED_BY LAST_UPDATE_DAT LAST_UPDATED_BY
- --------------- ---------- --------------- ---------------
1
PROQUEST_STATUS_12_12_2018.xml
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
</ROW>
</ROWSET>
N Thu 21-Feb-2019 Thu 21-Feb-2019
2
PROQUEST_STATUS_12_13_2018.xml
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
</ROW>
</ROWSET>
N Thu 21-Feb-2019 Thu 21-Feb-2019
2 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
