Load XML file to an existent oracle table using oracle sql developer

Question

I´m trying to load an existent XML file into an existent table in oracle. My XML file as the structure:

enter image description here

<Invoice>
  <InvoiceNo>FS TML0001/003123501</InvoiceNo>
  <ATCUD>JFRWKFDT-003123501</ATCUD>
  <DocumentStatus>
    <InvoiceStatus>N</InvoiceStatus>
    <InvoiceStatusDate>2025-04-01T14:32:02</InvoiceStatusDate>
    <SourceID>98778400</SourceID>
    <SourceBilling>P</SourceBilling>
  </DocumentStatus>
  <Hash>LzIHA5diGz++Py1HCXPUph9hDT2aKNUDGa3bvxPlLwPpGsSsItEe2gEBLEfG6NjpLIep/d1VCg90lGAi67YhlwCpIObCe6T3GzDbXR8dSdlv0xuSFpZ9fsWnfmTgXM8uO9LRAW1Ki40GKMIdqppYt67wvPSrqtjPoIn2afvE7bI=</Hash>
  <HashControl>1</HashControl>
  <Period>04</Period>
  <InvoiceDate>2025-04-01</InvoiceDate>
  <InvoiceType>FS</InvoiceType>
  <SpecialRegimes>
    <SelfBillingIndicator>0</SelfBillingIndicator>
    <CashVATSchemeIndicator>0</CashVATSchemeIndicator>
    <ThirdPartiesBillingIndicator>1</ThirdPartiesBillingIndicator>
  </SpecialRegimes>
  <SourceID>98778400</SourceID>
  <EACCode>49310</EACCode>
  <SystemEntryDate>2025-04-01T14:31:30</SystemEntryDate>
  <CustomerID>307497321</CustomerID>
  <Line>
    <LineNumber>1</LineNumber>
    <ProductCode>CTG22_ProductGroup_5</ProductCode>
    <ProductDescription>Navegante Lisboa</ProductDescription>
    <Quantity>1</Quantity>
    <UnitOfMeasure>UN</UnitOfMeasure>
    <UnitPrice>28.3000</UnitPrice>
    <TaxPointDate>2025-04-01</TaxPointDate>
    <Description>3310713506</Description>
    <CreditAmount>28.3000</CreditAmount>
    <Tax>
      <TaxType>IVA</TaxType>
      <TaxCountryRegion>PT</TaxCountryRegion>
      <TaxCode>RED</TaxCode>
      <TaxPercentage>6.00</TaxPercentage>
    </Tax>
  </Line>
  <DocumentTotals>
    <TaxPayable>1.7000</TaxPayable>
    <NetTotal>28.3000</NetTotal>
    <GrossTotal>30.00</GrossTotal>
  </DocumentTotals>
</Invoice>


<Invoice>
  <InvoiceNo>FS TML0001/003123502</InvoiceNo>
  <ATCUD>JFRWKFDT-003123502</ATCUD>
  <DocumentStatus>
    <InvoiceStatus>N</InvoiceStatus>
    <InvoiceStatusDate>2025-04-01T14:32:04</InvoiceStatusDate>
    <SourceID>98774198</SourceID>
    <SourceBilling>P</SourceBilling>
  </DocumentStatus>
  <Hash>ZgSvYKf+Wtc8lSWTkHgpbnTy3ksT60gKT4umVcIfeQiW0G1MBySSGQ3foEzWFoPSSgA3ua49eTx/f9kUjM0+o4h0F0dEO7kF/xg938GT8ps3piqu1YX4V+nlcqWmysNGnrQGfSAPuIDeBO3Iq3dvGXHhoixl/8qx14kVtwzDai8=</Hash>
  <HashControl>1</HashControl>
  <Period>04</Period>
  <InvoiceDate>2025-04-01</InvoiceDate>
  <InvoiceType>FS</InvoiceType>
  <SpecialRegimes>
    <SelfBillingIndicator>0</SelfBillingIndicator>
    <CashVATSchemeIndicator>0</CashVATSchemeIndicator>
    <ThirdPartiesBillingIndicator>1</ThirdPartiesBillingIndicator>
  </SpecialRegimes>
  <SourceID>98774198</SourceID>
  <EACCode>49310</EACCode>
  <SystemEntryDate>2025-04-01T14:31:34</SystemEntryDate>
  <CustomerID>999999990</CustomerID>
  <Line>
    <LineNumber>1</LineNumber>
    <ProductCode>CTG22_ProductGroup_10</ProductCode>
    <ProductDescription>PAC navegante +65</ProductDescription>
    <Quantity>1</Quantity>
    <UnitOfMeasure>UN</UnitOfMeasure>
    <UnitPrice>0.00</UnitPrice>
    <TaxPointDate>2025-04-01</TaxPointDate>
    <Description>3310669876</Description>
    <CreditAmount>0.00</CreditAmount>
    <Tax>
      <TaxType>IVA</TaxType>
      <TaxCountryRegion>PT</TaxCountryRegion>
      <TaxCode>RED</TaxCode>
      <TaxPercentage>6.00</TaxPercentage>
    </Tax>
    <SettlementAmount>37.7359</SettlementAmount>
  </Line>
  <DocumentTotals>
    <TaxPayable>0.00</TaxPayable>
    <NetTotal>0.00</NetTotal>
    <GrossTotal>0.00</GrossTotal>
  </DocumentTotals>
</Invoice>

I just nedd a procedure that:

1- Read a file in my local computer and the stored procedure receive a parameter and this parameter is the file in my local computer (C:\tmp p.e.)

2- read the file and insert in table xml_final_table the content of each tag in header. (LineNumber, ProductCode.....) 3- I do not want to load the all file to a table and after to a final table. I want to load all the information to a final table.

I already have the code:

DECLARE
  x XMLType := XMLType(
    '<?xml version="1.0" ?> 
<person>
   <row>
       <name>Tom</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
   <row>
       <name>Jim</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
</person>');
BEGIN
  FOR r IN (
    SELECT ExtractValue(Value(p),'/row/name/text()') as name
          ,ExtractValue(Value(p),'/row/Address/State/text()') as state
          ,ExtractValue(Value(p),'/row/Address/City/text()') as city
    FROM   TABLE(XMLSequence(Extract(x,'/person/row'))) p
    ) LOOP
    -- do whatever you want with r.name, r.state, r.city
  END LOOP;
END; 

But is not solving my problem and i´m lost who can I do that?

Any help please?

Answer 1

Given your sample code, you can use XMLTABLE and an INSERT INTO ... SELECT ... statement to parse the XML and insert it into the table:

DECLARE
  x XMLType := XMLType(
    '<?xml version="1.0" ?> 
<person>
   <row>
       <name>Tom</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
   <row>
       <name>Jim</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
</person>');
BEGIN
  INSERT INTO table_name (name, state, city)
  SELECT name, state, city
  FROM   XMLTABLE(
           '/person/row'
           PASSING x
           COLUMNS
             name  VARCHAR2(50) PATH './name',
             state VARCHAR2(50) PATH './Address/State',
             city  VARCHAR2(50) PATH './Address/City'
         );
END;
/

If you want to put the code into a procedure the replace the DECLARE clause with the CREATE PROCEDURE signature.

After running the PL/SQL block, the table will contain:

NAME	STATE	CITY
Tom	California	Los angeles
Jim	California	Los angeles

If you have XML in a different structure then adapt the paths to your data; this is left as an exercise to the reader.

fiddle