Loading XML data gets error saying my control file

Question

I am getting an error loading an XML document from a text file into an Oracle table using SQL*Loader.

I have created an XML table:

CREATE TABLE TEST_XML OF XMLTYPE
XMLTYPE STORE AS SECUREFILE BINARY XML;

And I have a file test_file.xml:

<ROWSET>
<ROW>
<ID>1</ID>
<TEXT>This is some text</TEXT>
</ROW>
<ROW>
<ID>2</ID>
<TEXT>This is some more text</TEXT>
</ROW>
<ROW>
<ID>3</ID>
<TEXT>This is some other text</TEXT>
</ROW>
<ROW>
<ID>4</ID>
<TEXT>This is also some text</TEXT>
</ROW>
</ROWSET>

I have created a control file test_loading.ctl:

LOAD DATA 
INFILE "test_file.xml"
append INTO TABLE TEST_XML 
xmltype(XMLDATA)
(
    XMLDATA LOBFILE("test_file.xml") TERMINATED BY EOF
)

When I run SQL*Loader using that control file:

sqlldr username/password control=/path/test_loading.ctl

it returns with the following error:

SQL*Loader-416: SDF clause for field XMLDATA in table TEST_XML_ARUN references a non existent field.

What am I doing wrong?

Answer 1

You seem to be mixing up a few ways of doing this. The error is because it's trying to interpret the "test_file.xml" inside LOBFILE() as a field reference.

If you know you will only load one XML document from a single text file you can make your control file:

LOAD DATA
INFILE *
append INTO TABLE TEST_XML
XMLType(XMLDATA)
FIELDS
(
    FILL FILLER CHAR(1),
    XMLDATA LOBFILE(CONSTANT test_file.xml) TERMINATED BY EOF
)
BEGINDATA
0

The BEGINDATA section has a row with a filler character for each XML doc in the file, and as there's only one, there's a single filler.

Note the CONSTANT which makes it look for a file called that, not a field. The log file shows that static name:

Table TEST_XML, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FILL                                FIRST     1           CHARACTER
  (FILLER FIELD)
XMLDATA                           DERIVED     *  EOF      CHARACTER
    Static LOBFILE.  Filename is test_file.xml


Table TEST_XML:
  1 Row successfully loaded.
  0 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.

To use a field you would have a data file with the file name, lets call it test_loading.dat to match the control file name, which contains:

test_file.xml

And a control file which uses that as the INFILE, and the content of its first field as the file name:

LOAD DATA
INFILE test_loading.dat
append INTO TABLE TEST_XML
XMLType(XMLDATA)
FIELDS
(
    filename FILLER CHAR(30),
    XMLDATA LOBFILE(filename) TERMINATED BY EOF
)

This time the log file shows the name is being retrieved dynamically:

Table TEST_XML, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FILENAME                            FIRST    30           CHARACTER
  (FILLER FIELD)
XMLDATA                           DERIVED     *  EOF      CHARACTER
    Dynamic LOBFILE.  Filename in field FILENAME


Table TEST_XML:
  1 Row successfully loaded.
  0 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.

Read more in the documentation.

Either will work for you. As you only have a single file in your example the first version might be slightly simpler, but if you will be loading multiple files (with a table row per file) the second version is more useful.