XML data insert through sql loader

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"?>
<Customers>
  <Customer>
       <CustID>1</CustID>
       <Company>Bell South</Company>  
       <City>New York</City>
  </Customer>
  <Customer>
       <CustID>2</CustID>
       <Company>Barnes &amp; Noble</Company>  
       <City>New York</City>
  </Customer>
  <Customer>
       <CustID>3</CustID>
       <Company>Comp USA</Company>  
       <City>Tampa</City>
  </Customer>  
  <Customer>
       <CustID>4</CustID>
       <Company>Borders</Company>      
       <City>Charlotte</City>
  </Customer>
</Customers>


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>'"
truncate

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"?>
<Customers>
  <Customer>
       <CustID>1</CustID>
       <Company>Bell South</Company>  
       <City>New York</City>
  </Customer>
</Customers></Customer>


*** And the log;

SQL*Loader: Release 9.0.1.1.1 - 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            
  (FILLER FIELD)
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)

Table CUSTOMERS:
  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