SQL Loader Issue

Some of the columns that I am trying to load into Oracle via SQL Loader from an XML file are null. For example may have several filled but ocassi开发者_运维百科onally it has some nulls.

How can I tell the SQL*Loader that some of the data will be null, or how can I deal with nulls?

---

Like jonearles suggested. I use XMLTABLE to insert XML data into relational tables. First put the XML data into a Oracle tables XMLTYPE column:

DROP TABLE XMLTEST;

CREATE TABLE XMLTEST
(   XML_COL XMLTYPE);

DECLARE
  poXML CLOB; 
BEGIN   
  -- Store the Purchase Order XML in the CLOB variable
  poXML := '<?xml version="1.0"?>
<zalen>
    <zaal zaal_id="1">
        <alt_id>88</alt_id>
        <display_naam>01 West 430</display_naam>
        <alt_db>exp_BB</alt_db>
    </zaal>
    <zaal zaal_id="2">
        <alt_id>170</alt_id>
        <display_naam>02 Midden 010</display_naam>
        <alt_db>exp_BB</alt_db>
    </zaal>
    <zaal zaal_id="3">
        <alt_id>173</alt_id>
        <display_naam>02 Midden 110</display_naam>
        <alt_db>exp_BB</alt_db>
    </zaal>
    <zaal zaal_id="4">
        <syl_id>F491B0A119DABE76B2F6B2C0A3E902F6</syl_id>
        <alt_id>183</alt_id>
        <display_naam>02 Oost 010</display_naam>
        <alt_db>exp_BB</alt_db>
    </zaal>
    <zaal zaal_id="5">
        <alt_id>172</alt_id>
        <display_naam>02 Oost 300</display_naam>
        <alt_db>exp_BB</alt_db>
    </zaal>
  .
  .
  .
    <zaal zaal_id="126">
        <syl_id>F491B0A119DABE76B2F6B2C0A3E901E3</syl_id>
        <alt_id>129</alt_id>
        <display_naam>HB.02.140</display_naam>
        <alt_db>exp_EE</alt_db>
    </zaal>
</zalen>';

  INSERT INTO xmltest (xml_col) VALUES (XMLTYPE(poXML));

END;
/

Use XMLTable function to create (or insert into) the table:

drop table zalen;
create table zalen as 
select xt.zaal_id
,      xt.alt_id
,      xt.syl_id
,      xt.alt_db
,      xt.display_naam
from xmltest xts
,    XMLTable('zalen/zaal' PASSING xts.xml_col 
                   columns zaal_id INTEGER PATH '@zaal_id'
                             ,alt_id INTEGER PATH 'alt_id'
                             ,syl_id VARCHAR2(100) PATH 'syl_id'
                             ,display_naam VARCHAR2(100)PATH 'display_naam'
                             ,alt_db VARCHAR2(100)PATH 'alt_db') xt;

---

You may want to rethink the way you're loading and transforming files. I assume this is related to your other question, in which case it looks like you're building your own XML parser. Oracle provides some tools to help you do this.

First, just to load the file, you can either use SQL*Loader to load the data as an XMLType, or you can use something like DBMS_XSLPROCESSOR.READ2CLOB to read the file as a CLOB and then convert it to an XMLType.

After it's loaded as an XMLType, you can use a PL/SQL procedure that uses XPath to iterate through the values and insert them into the table.

And there are probably some other ways to do it.

I'm sure none of these ideas are nearly as easy as what you were hoping for. Processing XML correctly is much more complicated than what SQL*Loader is typically used for - loading delimited files.