Oracle SQL*Loader getting CDATA values...

Question

Anybody knows how to do this? I know there's a better way of loading XML data to Oracle without using SQL*Loader, but I'm just curious on how this is done using it. I have already a code that can load XML data to the DB, however, it wont run if the XML file has values that contain a CDATA...

Below is the control file code which works if the values are not CDATA...

LOAD DATA  
INFILE FRATS.xml "str '</ROW>'"  
APPEND  
INTO TABLE "FRATERNITIES"  
(  
    DUMMY FILLER TERMINATED BY "<ROW>",  
    THE_CODE SEQUENCE (MAX, 1),  
    DUMMY2 FILLER TERMINATED BY "</COLUMN>",  
    STORE_NN_KJ ENCLOSED BY '<COLUMN NAME="THE_NAME">' AND '</COLUMN>',  
    STAFF_COUNT ENCLOSED BY '<COLUMN NAME="THE_COUNT">' AND '</COLUMN>'  
)

Here's the XML file:
<?xml version='1.0' encoding='MS932' ?>
<RESULTS>
<ROW>
<COLUMN NAME="THE_CODE">777</COLUMN>
<COLUMN NAME="THE_NAME">CharlieOscarDelta</COLUMN>
<COLUMN NAME="THE_COUNT">24</COLUMN>
</ROW>
</RESULTS>

Here's the XML file with CDATA values. My control file will not run with it...:
<?xml version='1.0' encoding='MS932' ?>
<RESULTS>
<ROW>
<COLUMN NAME="THE_CODE"><![CDATA[777]]></COLUMN>
<COLUMN NAME="THE_NAME"><![CDATA[CharlieOscarDelta]]></COLUMN>
<COLUMN NAME="THE_COUNT"><![CDATA[24]]></COLUMN>
</ROW>
</RESULTS>

Answer 1

have you tried

STORE_NN_KJ "substr(substr(:STORE_NN_KJ,instr(:STORE_NN_KJ,'<![CDATA[')+9),0,instr(substr(:STORE_NN_KJ,instr(:STORE_NN_KJ,'<![CDATA[')+9),']]>'))" ENCLOSED BY '<COLUMN NAME="THE_NAME">' AND '</COLUMN>'

EDIT Looks like I forgot a ).. Try this..