Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13279
  • Last Modified:

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"?>
       <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    

1 Solution
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!
can you try this?:

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

I believe the idea is not to skip the first record, but to load it without errors...
Technology Partners: 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!

it skip the first line , not the first records.
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...
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...
DaveESAuthor Commented:

I changed it to:

dummy filler terminated by "<Customer>",

and all is well.


Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now