XML data insert through sql loader

Posted on 2003-03-14
Medium Priority
Last Modified: 2007-12-19
Someone asked this question on 3/8/03 and got an answer that almost works and I was wondering if anyone knew why the first record is always rejected and what the fix is.

The original question;

create table customers(
custID number primary key,
company varchar2(100),
city varchar2(100));

and i have xml document as

<?xml version="1.0"?>
       <Company>Bell South</Company>  
       <City>New York</City>
       <Company>Barnes &amp; Noble</Company>  
       <City>New York</City>
       <Company>Comp USA</Company>  

I need to insert the values of custId,company,city into customers table in column values custid,company,city respectively.

I have to do thrugh sql loader facility, as i need to load large data from the xml file(large file).

***** The reply was:

use the "enclosed by" keyword:

your controlfile looks like this:

load data
infile 'yourfile.xml'  "str '</Customer>'"

into table customers
dummy filler terminated by ">",
custid enclosed by "<CustID>" and "</CustID>",
company enclosed by "<Company>" and "</Company>",
city enclosed by "<City>" and "</City>"

***** This works, but it skips the first record. Here is the .bad record;

<?xml version="1.0"?>
       <Company>Bell South</Company>  
       <City>New York</City>

*** And the log;

SQL*Loader: Release - Production on Fri Mar 14 10:54:26 2003

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Control File:   xmlspec2.ctl
Data File:      customers.txt
  File processing option string: "str '</Customer>'"
  Bad File:     customers.bad
  Discard File:  none specified
 (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 CUSTOMERS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DUMMY                               FIRST     *   >       CHARACTER            
CUSTID                               NEXT     *           CHARACTER            
    First enclosure string : '<CustID>'
    Second enclosure string : '</CustID>'
COMPANY                              NEXT     *           CHARACTER            
    First enclosure string : '<Company>'
    Second enclosure string : '</Company>'
CITY                                 NEXT     *           CHARACTER            
    First enclosure string : '<City>'
    Second enclosure string : '</City>'

Record 1: Rejected - Error on table CUSTOMERS, column CUSTID.
Initial enclosure character not found
Record 5: Rejected - Error on table CUSTOMERS, column CUSTID.
Column not found before end of logical record (use TRAILING NULLCOLS)

  3 Rows successfully loaded.
  2 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:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Fri Mar 14 10:54:26 2003
Run ended on Fri Mar 14 10:54:30 2003

Elapsed time was:     00:00:04.04
CPU time was:         00:00:00.03    

Question by:DaveES
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Accepted Solution

pennnn earned 150 total points
ID: 8137317
I guess it's because the filler field is terminated by ">". So for the first record the first ">" character it finds is on the first line:
<?xml version="1.0"?>
It expects then the initial encoluse character ("<CustID>") for the second field, but instead it finds <Customers>.
So I think that if you change the terminating character for the dummy field to the correct one it might work:
dummy filler terminated by "<Customer>",

Actually I've never tried using a whole string as a terminator, instead of a single character, but I think it should work.
Hope that helps!

Expert Comment

ID: 8138888
can you try this?:

c:>sqlldr ........ skip=1

LVL 11

Expert Comment

ID: 8138974
I believe the idea is not to skip the first record, but to load it without errors...
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Expert Comment

ID: 8142951
it skip the first line , not the first records.
LVL 11

Expert Comment

ID: 8143305
From the manual:

"SKIP (records to skip)
Default: No records are skipped.

SKIP specifies the number of logical records from the beginning of the file that should not be loaded."

Even if it were number of lines, not records, we'd still need to skip the first 2 lines, not the first one...
LVL 21

Expert Comment

ID: 8146872
You can use sqlloader to load the XML as XMLtype column and then convert it to the ascii data with XMLSave or use URIType.get_clob to do get the XML into cached temporary clob and then parse it with XMLParser,any of these looks more appropriate for the task...

Author Comment

ID: 8160441

I changed it to:

dummy filler terminated by "<Customer>",

and all is well.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question