Importing XML Data into an Existing Oracle Table via SQL Developer

Data and Databases
1

I’m having trouble importing XML data into my Oracle table. The XML file is currently located on my computer. Below is an example of the XML structure:

<?xml version="1.0" encoding="UTF-8"?>
<DataSet xmlns="urn:MyCompany:DataFormat:v1.0">
  <Metadata>
    <Version>1.0</Version>
    <CompanyInfo>
      <Name>MetroTransit Systems</Name>
      <TaxID>987654321</TaxID>
      <Address>
        <Street>123 Main Street</Street>
        <City>Metropolis</City>
        <PostCode>12345</PostCode>
      </Address>
    </CompanyInfo>
    <ReportPeriod>
      <StartDate>2026-01-01</StartDate>
      <EndDate>2026-01-31</EndDate>
    </ReportPeriod>
  </Metadata>
  <TransactionRecord>
    <ID>TR001</ID>
    <Date>2026-01-15</Date>
    <Type>Sale</Type>
    <CustomerID>C12345</CustomerID>
    <Items>
      <Item>
        <Code>PASS-MONTH</Code>
        <Description>Monthly Transit Pass</Description>
        <Quantity>1</Quantity>
        <UnitPrice>75.00</UnitPrice>
        <TaxRate>5.00</TaxRate>
      </Item>
    </Items>
    <Total>78.75</Total>
  </TransactionRecord>
</DataSet>

I’ve created a directory and written a procedure to load this XML into the table, but I keep getting an error. Here is my code:

CREATE DIRECTORY xml_files AS '/home/user/xmldata';

CREATE OR REPLACE PROCEDURE load_xml_data IS
  v_xml XMLTYPE;
  v_file BFILE := BFILENAME('XML_FILES', 'transit_data.xml');
BEGIN
  DBMS_LOB.OPEN(v_file, DBMS_LOB.LOB_READONLY);
  v_xml := XMLTYPE(v_file);
  
  INSERT INTO transit_transactions (
    transaction_id, 
    transaction_date, 
    customer_id, 
    item_code, 
    item_description, 
    quantity, 
    unit_price, 
    total_amount
  )
  SELECT 
    x.transaction_id,
    x.transaction_date,
    x.customer_id,
    x.item_code,
    x.item_description,
    x.quantity,
    x.unit_price,
    x.total_amount
  FROM XMLTABLE(
    '/DataSet/TransactionRecord'
    PASSING v_xml
    COLUMNS
      transaction_id VARCHAR2(10) PATH 'ID',
      transaction_date DATE PATH 'Date',
      customer_id VARCHAR2(10) PATH 'CustomerID',
      item_code VARCHAR2(20) PATH 'Items/Item/Code',
      item_description VARCHAR2(50) PATH 'Items/Item/Description',
      quantity NUMBER PATH 'Items/Item/Quantity',
      unit_price NUMBER PATH 'Items/Item/UnitPrice',
      total_amount NUMBER PATH 'Total'
  ) x;
  
  COMMIT;
  DBMS_LOB.CLOSE(v_file);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    DBMS_LOB.CLOSE(v_file);
    RAISE;
END;
/

I don’t understand what I’m doing wrong. The error message isn’t very clear. Any help would be appreciated!

2

hey mate, have u tried using XMLTABLE with namespace? somethin like this:

XMLTABLE(
XMLNAMESPACES(DEFAULT ‘urn:MyCompany:DataFormat:v1.0’),
‘/DataSet/TransactionRecord’
PASSING v_xml
– rest of ur stuff
)

might solve ur problem. lemme kno if it works!

3

hiya! have u tried using the EXTRACT function instead? it might be easier to handle namespaces that way. like this:

v_xml.EXTRACT(‘/DataSet/TransactionRecord/ID/text()’, ‘urn:MyCompany:DataFormat:v1.0’).getStringVal()

what do u think? could be worth a shot! let me kno if u need more help :slight_smile:

4

I’ve encountered similar issues when importing XML data into Oracle. One potential problem is that your XML has a default namespace, which your XMLTABLE query doesn’t account for. Try modifying your XMLTABLE function to include the namespace:

XMLTABLE(
    XMLNAMESPACES(DEFAULT 'urn:MyCompany:DataFormat:v1.0'),
    '/DataSet/TransactionRecord'
    PASSING v_xml
    -- rest of your XMLTABLE definition
)

Also, ensure that your table structure matches the data types in your INSERT statement. If the error persists, you might want to add more detailed error handling in your procedure. Consider using DBMS_OUTPUT.PUT_LINE to print the XML content and specific error information. This can help pinpoint where exactly the import is failing.