Forum Stats

  • 3,825,740 Users
  • 2,260,558 Discussions
  • 7,896,655 Comments

Discussions

How to load XML data using SQLLDR for XMLType column tables

User_8T7MY
User_8T7MY Member Posts: 1 Green Ribbon

We have a table which has one XMLType column & we are trying to load data into this table using SQL*LDR.

 

DDL -

 

CREATE TABLE schema.TABLE1

(COL1               CHAR(10 CHAR) NOT NULL

,COL2               CHAR(23 CHAR) NOT NULL

,COL_XMLTYPE        XMLTYPE NOT NULL

, CONSTRAINT CN751 PRIMARY KEY (COL1))

TABLESPACE TS_AA;

 

CTL File –


OPTIONS (DIRECT=TRUE)

LOAD DATA

INFILE '../data/TABLE1.dat'

BADFILE '../data/TABLE1.bad'

TRUNCATE

INTO TABLE TABLE1

XMLTYPE(COL_XMLTYPE)

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

COL1 CHAR(10),

COL2 CHAR(23) ,

filename FILLER CHAR(120),

COL_XMLTYPE LOBFILE(filename) TERMINATED BY EOF

)

 

DAT file –


"0000000558","2010-09-07T11:33:09.800","/home/oracle/Load_Data/data/IG57MT99.XML"

"0000006669","2010-09-07T11:33:10.210","/home/oracle/Load_Data/data/IG57MT99.XML"

"0000003330","2010-09-07T11:33:10.270","/home/oracle/Load_Data/data/IG57MT99.XML"

"0000055551","2010-09-07T11:33:10.350","/home/oracle/Load_Data/data/IG57MT99.XML"

 

Case 1-

When we execute the above combination as-is, we get error  - SQL*Loader-588: XMLTYPE directive not allowed for table TABLE1

 

We modified the DDL to -

CREATE TABLE schema.TABLE1

(COL1                      CHAR(10 CHAR) NOT NULL

, COL2          CHAR(23 CHAR) NOT NULL

,COL_XMLTYPE             XMLTYPE NOT NULL

, CONSTRAINT CN751 PRIMARY KEY (COL1))

 XMLType COLUMN COL_XMLTYPE STORE AS CLOB

(TABLESPACE TS_AA)

 

But again same error - SQL*Loader-588: XMLTYPE directive not allowed for table TABLE1

 

Case 2-

We remove the command - XMLTYPE(COL_XMLTYPE) from CTL file and run again

 

Record 1: Rejected - Error on table TABLE1, column COL_XMLTYPE.

ORA-39830: XML parsing failed

LPX-00231: invalid character 76 ('L') found in a Name or Nmtoken

 

Question –

  1. Which is the correct DDL for XMLTYPE column tables
  2. What is the correct CTL file configuration for XMLTYPE column tables
  3. Do you have any insights on how to resolve the errors in case 1 & 2


Tagged:

Answers

  • cormaco
    cormaco Member Posts: 1,940 Silver Crown
    edited Feb 16, 2022 12:53PM

    Error 588 means you need a table of type XMLTYPE to use XMLTYPE clause in the controlfile.

    The second error indicates a syntax error in your xml-file.

    Here I have created a working example:

    DDL:
    create table TABLE1
    (COL1               CHAR(10 CHAR) not null
    ,COL2               CHAR(23 CHAR) not null
    ,COL_XMLTYPE        XMLTYPE not null
    , constraint CN751 primary key (COL1))
    
    CTL:
    LOAD DATA
    INFILE 'TABLE1.dat'
    BADFILE 'TABLE1.bad'
    TRUNCATE
    INTO TABLE TABLE1
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    COL1 CHAR(10),
    COL2 CHAR(23) ,
    filename FILLER CHAR(120),
    COL_XMLTYPE LOBFILE(filename) TERMINATED BY EOF
    )
    
    DAT:
    "0000000558","2010-09-07T11:33:09.800","C:\DATA\Test.xml"
    
    XML:
    <?xml version='1.0' encoding='UTF-8' ?>
    <Test>Test</Test>
    
    LOG:
    SQL*Loader: Release 19.0.0.0.0 - Production on Wed Feb 16 13:32:49 2022
    Version 19.3.0.0.0
    Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
    
    Control File:   test.ctl
    Data File:      TABLE1.dat
      Bad File:     TABLE1.bad
      Discard File:  none specified
     
     (Allow all discards)
    
    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Bind array:     250 rows, maximum of 1048576 bytes
    Continuation:    none specified
    Path used:      Conventional
    
    Table TABLE1, loaded from every logical record.
    Insert option in effect for this table: TRUNCATE
    TRAILING NULLCOLS option in effect
    
       Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    COL1                                FIRST    10   ,  O(") CHARACTER            
    COL2                                 NEXT    23   ,  O(") CHARACTER            
    FILENAME                             NEXT   120   ,  O(") CHARACTER            
      (FILLER FIELD)
    COL_XMLTYPE                       DERIVED     *  EOF      CHARACTER            
        Dynamic LOBFILE.  Filename in field FILENAME
    
    Table TABLE1:
      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.
    
    Space allocated for bind array:                  42000 bytes(250 rows)
    Read   buffer bytes: 1048576
    
    Total logical records skipped:          0
    Total logical records read:             1
    Total logical records rejected:         0
    Total logical records discarded:        0
    
    Run began on Wed Feb 16 13:32:49 2022
    Run ended on Wed Feb 16 13:32:52 2022
    
    Elapsed time was:     00:00:03.11
    CPU time was:         00:00:00.12
    
    SQL:
    select * from table1
    
    COL1       COL2                    COL_XMLTYPE                                                                                                       
    ---------- ----------------------- -----------------------------------------
    0000000558 2010-09-07T11:33:09.800 <?xml version="1.0" encoding="UTF-8"?>
                                       <Test>Test</Test>