Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
Hello,
I need help with using SQL Loader to insert XML into a tables CLOB column.
The XML file is coming from a vendor into an FTP directory.
The file will be downloaded into a directory on the network.
There is a batch process which calls SQL Loader to insert the XML into a CLOB column in a table.
I've never used SQL Loader and of course I'm very stuck.
The table is defined as:
CREATE TABLE pod_eligible_students
(
xml_file CLOB
,process_flag CHAR(1)
);
The XML is:
<?xml version="1.0" encoding="UTF-8"?>
<Examinee_Registration_Batch create_date="2016-02-17" batch_id="013015111628" xsi:noNamespaceSchemaLocation="https://xxx.org/Schemas/xx/Examinee_Registration_Batch.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Examinee ATT_Num="007598058">
<Last_Name>Flinstone</Last_Name>
<First_Name>Fred</First_Name>
<DOB>1986-04-08</DOB>
<Email>fflinstone@xxx.com</Email>
<Phone>111-111-1111</Phone>
<School_Code>1</School_Code>
</Examinee>
<Examinee ATT_Num="007598007">
<Last_Name>Rubble</Last_Name>
<First_Name>Barney</First_Name>
<DOB>1990-12-17</DOB>
<Email>brubble@xxx.com</Email>
<Phone>222-222-2222</Phone>
<School_Code>5</School_Code>
</Examinee>
</Examinee_Registration_Batch>
The Control file (pod_elig_students.ctl) I've attempted is:
LOAD DATA
INFILE *
APPEND
INTO TABLE pod_eligible_students
xmltype(xml_file)
(
filename FILLER CHAR(30),
xml_file LOBFILE(filename) TERMINATED BY EOF,
process_flag CONSTANT 'N'
)
The SQL Loader command I've tried is:
sqlldr control=pod_elig_students.ctl userid=xxx/xxx@xxx.world skip=1 errors=1000 data=m:\Pod\download\ELIG-STUDENTS-013020151117AM.xml >> m:\Pod\Logs\pod_elig_students.log
Nothing is getting loaded into the POD_ELIGIBLE_STUDENTS table. There's no error.
Can someone help with how to construct the Control file and also for the file name can the it be changed to ELIG-STUDENTS-*.xml or maybe can just *.xml be used?
What else can I provide?
Thanks,
Joe