CODEWITHSUNDEEP
xmloracle-database
user3716325
user3716325

Reputation: 1

Insert XML file into Oracle table

I have XML file containing 13 columns. I want to load the file in Oracle table which has same columns as the file. I tried inserting it with external table. It loads half the number of records in XML file as some tags are missing

I get the error message in log file as

KUP-04021: field formatting error for field PREVIOUSCUSTOMEREXPERIENCE KUP-04035: beginning enclosing delimiter not found KUP-04101: record 445 rejected in file E:\Oracle\Reddy_Test_Dir\resources.xml

For this record the tag was missing in the XML file.

Can anybody suggest some way how to load all these records

Thanks :)

Upvotes: 0

Views: 781

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Where is the problem.

I think you are defined the external table in that way.

create table bleble ORGANIZATION EXTERNAL belbe...
  ACCESS PARAMETERS (
      RECORDS DELIMITED BY '</row>'
      FIELDS (
         Col1 CHAR(100) ENCLOSED BY '<Name>' AND '</Name>',
         col2 CHAR(100) ENCLOSED BY '<Name2>' AND '</Name2>')
  )

And xml looks like 

<rowset>
  <row>
    <name>aa</name>
    <name2>aa</name2>
  </row>
  <row>
    <name>bb</name>
  </row>
</rowset>

External table can't find enclosing delimiter for col2 and it throw error.

My recommendation is to look for other way to deal with xml.

  1. DBMS_XMLSTORE or DBMS_XMLSAVE.
  2. Oracel XDB repository. (quite hard)
  3. Import and save document in xmltype. And xmltype can be processed by xmltable.

Upvotes: 1

Related Questions