Can Oracle SQL*Loader process XML?

Question

Does Oracle offer a standardized upload of XML formatted files? I thought that the canonical format that is used for XML output, structure = ROWSET/ROW/columname, could be uploaded back into the table again, by just running sqlldr with appropriate control file contents.
But I cannot find anything about this anywhere on the web, and error messages after trials seem to indicate that it is only possible to upload XML into XML-type formatted tables, where I just want to upload data in a plain table but supply the data in XML format.

score 1 · Accepted Answer · answered Aug 31 '12 at 07:54

1

No, SQL*Loader can only process "flat" files.

One option is to write an XSLT transformation that turns the ROWSET/ROW/column format into a text file and then import that into the target table.

Another option is to import the XML into a single row, and then use Oracle's XML functions to select a relational result from that staging table and insert it into the real table.

answered Aug 31 '12 at 07:54

OK - so it's back to using files with a proper chosen column delimiter then. Pity and a bit unexpected - and a hint to Oracle to add this functionality in 11.3 :-). – Maestro13 Aug 31 '12 at 07:58
1

Oracle begs to differ: http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb25loa.htm and http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb25loa.htm#ADXDB2900 – Sean B. Durkin Oct 29 '13 at 03:48
@SeanB.Durkin: those examples are about loading the content of an XML file (or fragment) into a single `XMLType` column. It's not about parsing an XML structure and putting the contents of individual tags into different columns. The XML: `42Arthur` can **not** be processed by SQLLoader into a table `person(id, name)` – Oct 29 '13 at 07:21

score 1 · Answer 2 · answered Aug 31 '12 at 20:49

There isn't a standardised option, but with this specific format you can do it. If you have a table:

CREATE TABLE test_tab (
    id NUMBER,
    text VARCHAR2(50)
);

And your records in a test_tab.xml file:

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

And a control file test_tab.ctl:

LOAD DATA
INFILE 'test_tab.xml'
CONCATENATE 4
INTO TABLE test_tab
(
    dummy FILLER CHAR(15) TERMINATED BY "<ROW>",
    id CHAR(10) ENCLOSED BY "<ID>" AND "</ID>",
    text CHAR(40) ENCLOSED BY "<TEXT>" AND "</TEXT>"
)

You can do:

sqlldr usr/pwd control=test_tab.ctl

Commit point reached - logical record count 4

SELECT * FROM test_tab;

        ID TEXT
---------- --------------------------------------------------
         1 This is some text
         2 This is some more text
         3 This is some other text
         4 This is also some text

You could also create an external table from the same file, if you put in a directory Oracle can see:

CREATE TABLE test_tab (
    id NUMBER,
    text VARCHAR2(50)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY some_dir
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY "</ROW>"
        FIELDS
        (
            dummy CHAR(15) TERMINATED BY "<ROW>",
            id CHAR(10) ENCLOSED BY "<ID>" AND "</ID>",
            text CHAR(40) ENCLOSED BY "<TEXT>" AND "</TEXT>"
        )
    )
    LOCATION ('test_tab.xml')
)
PARALLEL
REJECT LIMIT UNLIMITED;

Table created.

SELECT * FROM test_tab;

    ID TEXT
---------- --------------------------------------------------
     1 This is some text
     2 This is some more text
     3 This is some other text
     4 This is also some text

Definitely a good alternative. I will keep this in mind for later possible use! — Maestro13, Sep 01 '12 at 15:20
Tested this (adding an `append` keyword after the `into table` one to ensure the rows are added to a non-empty table) and it works like a charm! — Maestro13, Sep 03 '12 at 07:22
This will fail as soon as the XML document contains entities like '&', '<' or '>'. — jarnbjo, Jul 23 '13 at 08:30
@jambjo - it only seems to have a problem if there is more than one such entity, oddly, and I'm not sure why it has a problem at all since they're encoded. This would still work with that caveat, so it depends on the kind of data being used I suppose. Good catch though, and interesting point. — Alex Poole, Jul 23 '13 at 09:48
How are empty nodes handled? For example if you had ``` 3 ``` ? SQLLDR will barf on this given the sample control file. — alexherm, Nov 20 '19 at 16:21
@alexherm - SQL\*Loader doesn't understand XML; this approach is treating the node start/end tags as delimiters, hence "with this specific format". Using `` is not in the same format - remember this is plain text, not XML, so they are not the same thing at all. You would either need to us a transformation as in the accepted answer; or if you're loading as an external table you might be able to use the preprocessor directive to transform self-closing tags to open/close pairs. You'll need to ask a new question if you can't get either approach to work. — Alex Poole, Nov 20 '19 at 17:38

Can Oracle SQL*Loader process XML?

2 Answers2

Linked