Reading Nested XML Using SQL

NestedXML.sql

https://www.itjungle.com/2020/08/31/guru-reading-nested-xml-using-sql/


set transaction isolation level read committed;

select a.* from xmltable('INVOICES/INVOICE'
  passing (xmlparse(document get_xml_file('/SomeDir/INVOICE.XML')))
  columns
    InvoiceID varchar(6) Path 'HEADER/INVOICE_ID',
    CustomerName varchar(20) Path 'RECEIVER/CUSTOMER_INFORMATION/CUSTOMER_NAME',
    CustomerNumber varchar(10) Path 'RECEIVER/CUSTOMER_INFORMATION/CUSTOMER_ID'
 ) as a;
 
 select a.* from xmltable ('INVOICES/INVOICE/ROWS/ROW'
 Passing ( xmlparse(document get_xml_file('/SomeDir/INVOICE.XML')))
 columns
    ROWNUM  integer  Path 'ROW_NUMBER',
    PRODUCTID varchar(20) Path 'PRODUCT/PRODUCT_ID',
    PRODUCTNAME varchar(50) Path 'PRODUCT/PRODUCT_NAME'
 ) as a;
 
 with f1 as
   (select *
       from xmltable ('INVOICES/INVOICE'
             passing ( xmlparse(document get_xml_file('/SomeDir/INVOICE.XML')))
             columns
                InvoiceID   varchar(6)   Path 'HEADER/INVOICE_ID',
                ALLROWS     xml          Path 'ROWS')),
f2 as
   (select f1.*, p.*
      from f1,    
           xmltable ('ROWS/ROW' 
            passing ALLROWS
            columns 
               ROWNUM   integer   Path 'ROW_NUMBER',
               PRODUCTID varchar(20)  Path 'PRODUCT/PRODUCT_ID',
               PRODUCTNAME varchar(50) Path 'PRODUCT/PRODUCT_NAME') as P)
select f2.InvoiceID, f2.RowNum, f2.ProductID, f2.ProductName from f2;

<?xml version='1.0' encoding='UTF-8'? >
<INVOICES>
  <INVOICE>
    <HEADER>
     <INVOICE_ID>123456</INVOICE_ID>
       <CURRENCY>
           <CODE>EUR</CODE>
       </CURRENCY>
    </HEADER>
    <RECEIVER>
       <CUSTOMER_INFORMATION>
          <CUSTOMER_NAME>COMPANY1</CUSTOMER_NAME>
          <CUSTOMER_ID>00000001</CUSTOMER_ID>
          <ADDRESS>
             <STREET_ADDRESS1>Lane 1</STREET_ADDRESS1>
             <STREET_ADDRESS2></STREET_ADDRESS2>
             <STREET_ADDRESS3></STREET_ADDRESS3>
             <POSTAL_CODE>10</POSTAL_CODE>
             <COUNTRY>UK</COUNTRY>
          </ADDRESS>
       </CUSTOMER_INFORMATION>
    </RECEIVER>
    <ROWS>
       <ROW>
       <ROW_NUMBER>1</ROW_NUMBER>
           <PRODUCT>
              <PRODUCT_ID>111112</PRODUCT_ID>
              <PRODUCT_NAME>Some good stuff</PRODUCT_NAME>
          </PRODUCT>
           <ROW_TOTAL>
             <AMOUNT SIGN="+" VAT="EXCLUDED">10.000</AMOUNT>
           </ROW_TOTAL>
       </ROW>
       <ROW>
       <ROW_NUMBER>2</ROW_NUMBER>
           <PRODUCT>
             <PRODUCT_ID>111114</PRODUCT_ID>
             <PRODUCT_NAME>Some other good stuff</PRODUCT_NAME>
         </PRODUCT>
           <ROW_TOTAL>
             <AMOUNT SIGN="+" VAT="EXCLUDED">5.350</AMOUNT>
           </ROW_TOTAL>
       </ROW>
    </ROWS>
  </INVOICE>
  <INVOICE>
    <HEADER>
     <INVOICE_ID<123457</INVOICE_ID>
       <CURRENCY>
           <CODE>EUR</CODE>
       </CURRENCY>
    </HEADER>
    <RECEIVER>
      <CUSTOMER_INFORMATION>
          <CUSTOMER_NAME>COMPANY2</CUSTOMER_NAME>
          <CUSTOMER_ID>00000002</CUSTOMER_ID>
          <ADDRESS>
             <STREET_ADDRESS1>Lane 2</STREET_ADDRESS1>
             <STREET_ADDRESS2></STREET_ADDRESS2>
             <STREET_ADDRESS3></STREET_ADDRESS3>
             <POSTAL_CODE>20</POSTAL_CODE>
             <COUNTRY>UK</COUNTRY>
          </ADDRESS>
      </CUSTOMER_INFORMATION>
    </RECEIVER>
    <ROWS>
       <ROW>
       <ROW_NUMBER>1</ROW_NUMBER>
           <PRODUCT>
              <PRODUCT_ID>111112</PRODUCT_ID>
              <PRODUCT_NAME>Some good stuff</PRODUCT_NAME>
          </PRODUCT>
           <ROW_TOTAL>
             <AMOUNT SIGN="+" VAT="EXCLUDED">10.000</AMOUNT>
           </ROW_TOTAL>
       </ROW>
       <ROW>
       <ROW_NUMBER>2</ROW_NUMBER>
           <PRODUCT>
              <PRODUCT_ID>111115</PRODUCT_ID>
              <PRODUCT_NAME>Some more good stuff</PRODUCT_NAME>
           </PRODUCT>
           <ROW_TOTAL>
             <AMOUNT SIGN="+" VAT="EXCLUDED">5.350</AMOUNT>
           </ROW_TOTAL>
       </ROW>
    </ROWS>
  </INVOICE>
</INVOICES>