title: Load XML file in Oracle with SQL*Loader
body: <p>I am trying to load large XML files in Oracle 11g and parse them into a new table (I have the SQL queries for this part), but I am not being able to set the right settings in the SQL*Loader control file. I am kind of new with Oracle and after searching for solutions online and the Oracle instructions and not being able to solve my problem I am asking your help. I am using Windows 10 and SQL Developer 4.0.3.16 to run the queries to create the tables in Oracle.</p>
  
  <p>This question was answered in a previous post but the proposed solution is not working for me (<a href="https://stackoverflow.com/questions/39123037/when-trying-to-load-a-clob-to-an-oracle-table-loading-too-many-records">When trying to load a clob to an Oracle table, loading too many records</a>), however I am not being able to make them work.</p>
  
  <p>I have used both control files from the previous webpage and only the first one is working for me with the same problem (same XML inserted in several rows)
  I am running this code in the prompt:</p>
  
  <pre><code>sqlldr rc_tables/xxuser@xx.xxx.xx.xx/euxxxx control='control_file.ctl' log = 'data_log.log' 
  </code></pre>
  
  <p>Control file:</p>
  
  <pre><code>LOAD DATA
  CHARACTERSET UTF8
  INFILE 'C:\Users\...\NVTPOI_03_MEX_9701_001.xml'
  REPLACE
  INTO TABLE LEAD_REPORTING_CLOB
  TRAILING NULLCOLS
  (
  XML  LOBFILE(CONSTANT 'C:\...\NVTPOI_03_MEX_9701_001.xml') TERMINATED BY    EOF
  )
  </code></pre>
  
  <p>My created table is:</p>
  
  <pre><code>CREATE TABLE "LEAD_REPORTING_CLOB" 
  (SHARED_XML CLOB);
  </code></pre>
  
  <p>If I use the second approach (Resolution in the previous post) I get an error message if I decide to run it without adding the FILENAME field to the table:</p>
  
  <blockquote>
    <p>SQL*Loader-416: SDF clause for field SHARED_XML in table LEAD_REPORTING_CLOB references a non existent field.  </p>
  </blockquote>
  
  <p>Control file:</p>
  
  <pre><code>LOAD DATA
  INFILE * 
  REPLACE
  INTO TABLE LEAD_REPORTING_CLOB
  FIELDS TERMINATED BY ',' TRAILING NULLCOLS
  (
  SHARED_XML  LOBFILE(FILENAME) TERMINATED BY EOF
  )
  BEGINDATA
  'C:\Users\ivcrespo\Documents\projects\2018\oracle\NVTPOI_CHL_9500_001.xml'
  </code></pre>
  
  <p>I also tried the previous control file with a table that has the FILENAME char(100) field, but id did load nothing.</p>
  
  <p>control file:</p>
  
  <pre><code>LOAD DATA
  INFILE * 
  REPLACE
  
  INTO TABLE LEAD_REPORTING_CLOB
  FIELDS TERMINATED BY ',' TRAILING NULLCOLS
  (
  FILENAME,
  SHARED_XML  LOBFILE(FILENAME) TERMINATED BY EOF
  )
  BEGINDATA
  'C:\Users\ivcrespo\Documents\projects\2018\oracle\NVTPOI_CHL_9500_001.xml'
  
  
  SQL*Loader: Release 11.2.0.1.0 - Production on Wed Jul 4 08:39:35 2018
  
  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
  
  Control File:   control_file_xml_test2.ctl
  Data File:      control_file_xml_test2.ctl
  Bad File:       control_file_xml_test2.bad
  Discard File:   none specified
  (Allow all discards)
  
  Number to load: ALL
  Number to skip: 0
  Errors allowed: 50
  Bind array:     64 rows, maximum of 256000 bytes
  Continuation:   none specified
  Path used:      Conventional
  
  Table LEAD_REPORTING_CLOB, loaded from every logical record.
  Insert option in effect for this table: REPLACE
  TRAILING NULLCOLS option in effect
  
  Column Name                    Position   Len   Term Encl Datatype
  ------------------------------ ---------- ----- ---- ---- ---------------------
  FILENAME                            FIRST     *   ,       CHARACTER            
  SHARED_XML                        DERIVED     *  EOF      CHARACTER            
  Dynamic LOBFILE.  Filename in field FILENAME
  
  
  Table LEAD_REPORTING_CLOB:
    0 Rows 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:                  16512 bytes(64 rows)
  Read   buffer bytes: 1048576
  
  Total logical records skipped:          0
  Total logical records read:             0
  Total logical records rejected:         0
  Total logical records discarded:        0
  
  Run began on Wed Jul 04 08:39:35 2018
  Run ended on Wed Jul 04 08:39:37 2018
  
  Elapsed time was:     00:00:02.16
  CPU time was:         00:00:00.03
  </code></pre>
  
  <p>Can anyone help me to set this right? The examples that I have found online usually show the control file, but not the created table. Is there a mistake in my created table.
  This will be an example of the first node of the XML file(There can be hundred of them in each file):</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;DeliveryPackage xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xmlns:saxon="http://saxon.sf.net/"
               CreationTime="2018-06-18T13:07:40.196Z"
               VersionNo="Q318"&gt;
  &lt;POI&gt;
      &lt;Action&gt;Add&lt;/Action&gt;
      &lt;SupplierID&gt;3&lt;/SupplierID&gt;
      &lt;Identity&gt;
          &lt;POI_Entity_ID&gt;109034&lt;/POI_Entity_ID&gt;
          &lt;Names&gt;
              &lt;POI_Name Type="Official" Language_Code="SPA"&gt;
                  &lt;Text&gt;RAFAEL MURILLO VIDAL&lt;/Text&gt;
              &lt;/POI_Name&gt;
          &lt;/Names&gt;
          &lt;Category_ID Type="NT"&gt;9701&lt;/Category_ID&gt;
          &lt;Product_Type&gt;7&lt;/Product_Type&gt;
      &lt;/Identity&gt;
      &lt;Locations&gt;
          &lt;Location&gt;
              &lt;Address&gt;
                  &lt;ParsedAddress&gt;
                      &lt;ParsedStreetAddress&gt;
                          &lt;ParsedStreetName&gt;
                      &lt;StreetName Language_Code="SPA"&gt;RAFAEL MURILLO VIDAL&lt;/StreetName&gt;
                      &lt;StreetType Language_Code="SPA" Before="true" Attached="false"&gt;AVENIDA&lt;/StreetType&gt;
                          &lt;/ParsedStreetName&gt;
                      &lt;/ParsedStreetAddress&gt;
                      &lt;ParsedPlace&gt;
                          &lt;PlaceLevel2 Language_Code="SPA"&gt;VERACRUZ DE IGNACIO DE LA LLAVE&lt;/PlaceLevel2&gt;
                          &lt;PlaceLevel3 Language_Code="SPA"&gt;XALAPA&lt;/PlaceLevel3&gt;
                          &lt;PlaceLevel4 Language_Code="SPA"&gt;CUAUHTÉMOC&lt;/PlaceLevel4&gt;
                      &lt;/ParsedPlace&gt;
                      &lt;Zone Language_Code="SPA" Type="KD"&gt;XALAPA DE ENRÍQUEZ&lt;/Zone&gt;
                      &lt;PostalCode&gt;
                          &lt;NT_Postal&gt;91069&lt;/NT_Postal&gt;
                      &lt;/PostalCode&gt;
                      &lt;CountryCode&gt;MEX&lt;/CountryCode&gt;
                  &lt;/ParsedAddress&gt;
              &lt;/Address&gt;
              &lt;GeoPosition&gt;
                  &lt;Latitude&gt;19.51919&lt;/Latitude&gt;
                  &lt;Longitude&gt;-96.90392&lt;/Longitude&gt;
              &lt;/GeoPosition&gt;
              &lt;MapLinkID&gt;
                  &lt;LinkID&gt;919722915&lt;/LinkID&gt;
                  &lt;Side_of_Street&gt;R&lt;/Side_of_Street&gt;
                  &lt;Percent_from_RefNode&gt;30&lt;/Percent_from_RefNode&gt;
              &lt;/MapLinkID&gt;
              &lt;Confidence&gt;
                  &lt;Match_Level&gt;GeoPoint&lt;/Match_Level&gt;
              &lt;/Confidence&gt;
          &lt;/Location&gt;
      &lt;/Locations&gt;
      &lt;Details&gt;
          &lt;Camera&gt;
              &lt;CameraType&gt;Speed&lt;/CameraType&gt;
              &lt;SpeedLimit Unit="KPH"&gt;60&lt;/SpeedLimit&gt;
              &lt;DrivingDirection LinkHeading="T"&gt;110&lt;/DrivingDirection&gt;
          &lt;/Camera&gt;
      &lt;/Details&gt;
  &lt;/POI&gt;
  .
  . 
  
  &lt;/DeliveryPackage&gt;
  </code></pre>