title: Inserting rows into oracle with PL/SQL reading from XML CLOB
body: <p>I am having some real problems with PL/SQL.</p>
  
  <p>I have written a stored procedure which ready a record from an XML and inserts this into a database table.</p>
  
  <p>This compiles cleanly for single record input. But I am looking for the way to insert all the records of a large XML file and can't figure out how to make the look function correctly.</p>
  
  <p>Here is the code that is working for single record input:</p>
  
  <pre><code>    CREATE OR REPLACE EDITIONABLE PROCEDURE "DORTO"."DORTOSALES" (P_DE_SALES CLOB)
  AS
  I_ORTP VARCHAR2 (6);
  I_ORNO VARCHAR2 (20);
  I_IVNO NUMBER (9);
  I_CUNO VARCHAR2 (20);
  I_YREF VARCHAR2 (60);
  I_OREF VARCHAR2 (60);
  I_CUOR VARCHAR2 (40);
  I_ITCL VARCHAR2 (10);
  I_ITNO VARCHAR2 (30);
  I_IVQT NUMBER (15);
  I_BANO VARCHAR2 (40);
  I_IVDT NUMBER (8);
  I_DLIX NUMBER (11);
  I_ELNO VARCHAR2 (16);
  I_PONR NUMBER (5);
  I_CNNO VARCHAR2 (20);
  I_CNIV NUMBER (9);
  I_TRANSFER TIMESTAMP(6);
  BEGIN
  
    -- FOR i IN (
  
     SELECT EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/order/orderType')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/order/orderNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/order/customerNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/order/customerOrderReference')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/order/batchNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/order/deliveryNoteNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/order/elementNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/order/orderLineNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/creditNote/creditNoteOrderNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/creditNote/orderNumberCreditNote')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/creditNote/creditNoteNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/creditNote/creditNoteInvoiceNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/invoice/invoiceNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/invoice/itemClassification')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/invoice/itemMaterialNumber')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/invoice/invoicedQuantity')
    ,EXTRACTVALUE(xmltype(P_DE_SALES), '/dortoSalesInputIF/invoice/invoiceDate')
   INTO I_ORTP,I_ORNO,I_CUNO,I_CUOR,I_BANO,I_DLIX,I_ELNO,I_PONR,I_YREF,I_OREF,I_CNNO,I_CNIV,I_IVNO,I_ITCL,I_ITNO,I_IVQT,I_IVDT
   FROM TABLE(XMLSequence(xmltype(P_DE_SALES))) x ; --LOOP
   INSERT INTO DE_SALES (ORTP,ORNO,CUNO,CUOR,BANO,DLIX,ELNO,PONR,YREF,OREF,CNNO,CNIV,IVNO,ITCL,ITNO,IVQT,IVDT,TRANSFER) VALUES
   --(i.I_ORTP,i.I_ORNO,i.I_CUNO,i.I_CUOR,i.I_BANO,i.I_DLIX,i.I_ELNO,i.I_PONR,i.I_YREF,i.I_OREF,i.I_CNNO,i.I_CNIV,i.I_IVNO,i.I_ITCL,i.I_ITNO,i.I_IVQT,i.I_IVDT,sysdate);
  
   (I_ORTP,I_ORNO,I_CUNO,I_CUOR,I_BANO,I_DLIX,I_ELNO,I_PONR,I_YREF,I_OREF,I_CNNO,I_CNIV,I_IVNO,I_ITCL,I_ITNO,I_IVQT,I_IVDT,sysdate);
  
   --END LOOP;
  
   COMMIT;
  END;
  </code></pre>
  
  <p>You can see that I commented out my failed attempt at looping. Can somebody help me out here why my looping through the XML to then do the insert isn't working?</p>
  
  <p>Thanks,</p>
  
  <p>Tom</p>
  
  <hr>
  
  <p>Update - here is a sample XML</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="ISO-8859-1"?&gt;
  &lt;dortoSalesInputIF&gt;
      &lt;order&gt;
          &lt;orderType&gt;DO1&lt;/orderType&gt;
          &lt;orderNumber&gt;1304315&lt;/orderNumber&gt;
          &lt;customerNumber&gt;5104081&lt;/customerNumber&gt;
          &lt;customerOrderReference&gt;&lt;/customerOrderReference&gt;
          &lt;batchNumber&gt;&lt;/batchNumber&gt;
          &lt;deliveryNoteNumber&gt;8337697&lt;/deliveryNoteNumber&gt;
          &lt;elementNumber&gt;&lt;/elementNumber&gt;
          &lt;orderLineNumber&gt;2&lt;/orderLineNumber&gt;
      &lt;/order&gt;
      &lt;creditNote&gt;
          &lt;creditNoteOrderNumber&gt;617&lt;/creditNoteOrderNumber&gt;
          &lt;orderNumberCreditNote&gt;59237&lt;/orderNumberCreditNote&gt;
          &lt;creditNoteNumber&gt;&lt;/creditNoteNumber&gt;
          &lt;creditNoteInvoiceNumber&gt;&lt;/creditNoteInvoiceNumber&gt;
      &lt;/creditNote&gt;
      &lt;invoice&gt;
          &lt;invoiceNumber&gt;31615915&lt;/invoiceNumber&gt;
          &lt;itemClassification&gt;TRA&lt;/itemClassification&gt;
          &lt;itemMaterialNumber&gt;100000101&lt;/itemMaterialNumber&gt;
          &lt;invoicedQuantity&gt;1&lt;/invoicedQuantity&gt;
          &lt;invoiceDate&gt;20160531&lt;/invoiceDate&gt;
      &lt;/invoice&gt;
  &lt;/dortoSalesInputIF&gt;
  &lt;dortoSalesInputIF&gt;
      &lt;order&gt;
          &lt;orderType&gt;DO1&lt;/orderType&gt;
          &lt;orderNumber&gt;1304316&lt;/orderNumber&gt;
          &lt;customerNumber&gt;5104082&lt;/customerNumber&gt;
          &lt;customerOrderReference&gt;&lt;/customerOrderReference&gt;
          &lt;batchNumber&gt;&lt;/batchNumber&gt;
          &lt;deliveryNoteNumber&gt;8337698&lt;/deliveryNoteNumber&gt;
          &lt;elementNumber&gt;&lt;/elementNumber&gt;
          &lt;orderLineNumber&gt;2&lt;/orderLineNumber&gt;
      &lt;/order&gt;
      &lt;creditNote&gt;
          &lt;creditNoteOrderNumber&gt;618&lt;/creditNoteOrderNumber&gt;
          &lt;orderNumberCreditNote&gt;59238&lt;/orderNumberCreditNote&gt;
          &lt;creditNoteNumber&gt;&lt;/creditNoteNumber&gt;
          &lt;creditNoteInvoiceNumber&gt;&lt;/creditNoteInvoiceNumber&gt;
      &lt;/creditNote&gt;
      &lt;invoice&gt;
          &lt;invoiceNumber&gt;31615916&lt;/invoiceNumber&gt;
          &lt;itemClassification&gt;TRA&lt;/itemClassification&gt;
          &lt;itemMaterialNumber&gt;100000101&lt;/itemMaterialNumber&gt;
          &lt;invoicedQuantity&gt;1&lt;/invoicedQuantity&gt;
          &lt;invoiceDate&gt;20160531&lt;/invoiceDate&gt;
      &lt;/invoice&gt;
  &lt;/dortoSalesInputIF&gt;
  </code></pre>
  
  <p>UPDATE:</p>
  
  <p>I have tried the following - greatly simplifying the XML structure - including two reocrds to be inserted in the database.</p>
  
  <p>The single insert (with only record in the XML) works just fine, but it is the insert where I have two that is failing.</p>
  
  <p>I am getting the following error: </p>
  
  <p>ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence</p>
  
  <p>ORA-06512: at line 67</p>
  
  <p>ORA-06512: at line 67</p>
  
  <p>ORA-06512: at line 121</p>
  
  <pre><code>declare
    l_xml      xmltype := xmltype('&lt;?xml version="1.0" encoding="ISO-8859-1"?&gt;
  &lt;dortoSalesInputIF&gt;
      &lt;order&gt;
          &lt;orderType&gt;DO1&lt;/orderType&gt;
          &lt;orderNumber&gt;1304315&lt;/orderNumber&gt;
          &lt;customerNumber&gt;5104081&lt;/customerNumber&gt;
          &lt;customerOrderReference&gt;&lt;/customerOrderReference&gt;
          &lt;batchNumber&gt;&lt;/batchNumber&gt;
          &lt;deliveryNoteNumber&gt;8337697&lt;/deliveryNoteNumber&gt;
          &lt;elementNumber&gt;&lt;/elementNumber&gt;
          &lt;orderLineNumber&gt;2&lt;/orderLineNumber&gt;
          &lt;creditNoteOrderNumber&gt;617&lt;/creditNoteOrderNumber&gt;
          &lt;orderNumberCreditNote&gt;59237&lt;/orderNumberCreditNote&gt;
          &lt;creditNoteNumber&gt;&lt;/creditNoteNumber&gt;
          &lt;creditNoteInvoiceNumber&gt;&lt;/creditNoteInvoiceNumber&gt;
          &lt;invoiceNumber&gt;31615915&lt;/invoiceNumber&gt;
          &lt;itemClassification&gt;TRA&lt;/itemClassification&gt;
          &lt;itemMaterialNumber&gt;100000101&lt;/itemMaterialNumber&gt;
          &lt;invoicedQuantity&gt;1&lt;/invoicedQuantity&gt;
          &lt;invoiceDate&gt;20160531&lt;/invoiceDate&gt;
      &lt;/order&gt;
      &lt;order&gt;
          &lt;orderType&gt;DO1&lt;/orderType&gt;
          &lt;orderNumber&gt;1304316&lt;/orderNumber&gt;
          &lt;customerNumber&gt;5104082&lt;/customerNumber&gt;
          &lt;customerOrderReference&gt;&lt;/customerOrderReference&gt;
          &lt;batchNumber&gt;&lt;/batchNumber&gt;
          &lt;deliveryNoteNumber&gt;8337698&lt;/deliveryNoteNumber&gt;
          &lt;elementNumber&gt;&lt;/elementNumber&gt;
          &lt;orderLineNumber&gt;2&lt;/orderLineNumber&gt;
          &lt;creditNoteOrderNumber&gt;618&lt;/creditNoteOrderNumber&gt;
          &lt;orderNumberCreditNote&gt;59238&lt;/orderNumberCreditNote&gt;
          &lt;creditNoteNumber&gt;&lt;/creditNoteNumber&gt;
          &lt;creditNoteInvoiceNumber&gt;&lt;/creditNoteInvoiceNumber&gt;
          &lt;invoiceNumber&gt;31615916&lt;/invoiceNumber&gt;
          &lt;itemClassification&gt;TRA&lt;/itemClassification&gt;
          &lt;itemMaterialNumber&gt;100000101&lt;/itemMaterialNumber&gt;
          &lt;invoicedQuantity&gt;1&lt;/invoicedQuantity&gt;
          &lt;invoiceDate&gt;20160531&lt;/invoiceDate&gt;
      &lt;/order&gt;
  &lt;/dortoSalesInputIF&gt;');
  
  procedure DORTOSALES (i_clob in xmltype)
  AS
    I_ORTP     varchar2(6);
    I_ORNO     varchar2(20);
    I_IVNO     number(9);
    I_CUNO     varchar2(20);
    I_YREF     varchar2(60);
    I_OREF     varchar2(60);
    I_CUOR     varchar2(40);
    I_ITCL     varchar2(10);
    I_ITNO     varchar2(30);
    I_IVQT     number(15);
    I_BANO     varchar2(40);
    I_IVDT     number(8);
    I_DLIX     number(11);
    I_ELNO     varchar2(16);
    I_PONR     number(5);
    I_CNNO     varchar2(20);
    I_CNIV     number(9);
    I_TRANSFER timestamp(6);
  
  begin
  
    for i in  
    (
      SELECT  xt.*
      FROM    XMLTABLE ('/dortoSalesInputIF'
              PASSING  l_xml
              COLUMNS
                orderType               PATH '/dortoSalesInputIF/order/orderType',
                orderNumber             PATH '/dortoSalesInputIF/order/orderNumber',
                customerNumber          PATH '/dortoSalesInputIF/order/customerNumber',
                customerOrderReference  PATH '/dortoSalesInputIF/order/customerOrderReference',
                batchNumber             PATH '/dortoSalesInputIF/order/batchNumber',
                deliveryNoteNumber      PATH '/dortoSalesInputIF/order/deliveryNoteNumber',
                elementNumber           PATH '/dortoSalesInputIF/order/elementNumber',
                orderLineNumber         PATH '/dortoSalesInputIF/order/orderLineNumber',
                creditNoteOrderNumber   PATH '/dortoSalesInputIF/order/creditNoteOrderNumber',
                orderNumberCreditNote   PATH '/dortoSalesInputIF/order/orderNumberCreditNote',
                creditNoteNumber        PATH '/dortoSalesInputIF/order/creditNoteNumber',
                creditNoteInvoiceNumber PATH '/dortoSalesInputIF/order/creditNoteInvoiceNumber',
                invoiceNumber           PATH '/dortoSalesInputIF/order/invoiceNumber',
                itemClassification      PATH '/dortoSalesInputIF/order/itemClassification',
                itemMaterialNumber      PATH '/dortoSalesInputIF/order/itemMaterialNumber',
                invoicedQuantity        PATH '/dortoSalesInputIF/order/invoicedQuantity',
                invoiceDate             PATH '/dortoSalesInputIF/order/invoiceDate'
              ) xt
    )
    loop
      INSERT INTO DE_SALES (ORTP,ORNO,CUNO,CUOR,BANO,DLIX,ELNO,PONR,YREF,OREF,CNNO,CNIV,IVNO,ITCL,ITNO,IVQT,IVDT,TRANSFER) VALUES
     (i.orderType             ,
      i.orderNumber           ,
      i.customerNumber        ,
      i.customerOrderReference,
      i.batchNumber           ,
      i.deliveryNoteNumber    ,
      i.elementNumber         ,
      i.orderLineNumber       ,
      i.creditNoteOrderNumber ,
      i.orderNumberCreditNote ,
      i.creditNoteNumber      ,
      i.creditNoteInvoiceNumber,
      i.invoiceNumber         ,
      i.itemClassification    ,
      i.itemMaterialNumber    ,
      i.invoicedQuantity      ,
      i.invoiceDate           ,
      sysdate);
  
  
    END LOOP;
  
    COMMIT;
  END;
  
  
  begin
    DORTOSALES(l_xml);
  end;
  </code></pre>
  
  <p><strong><em>FINAL UPDATE</em></strong></p>
  
  <p>This is the one working</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE "DORTOSALES" (P_DE_SALES CLOB)
  AS
  BEGIN
      FOR record_in_xml IN (
          SELECT xt.*
          FROM XMLTABLE('/dortoSalesInputIF/order'
              PASSING XMLTYPE(P_DE_SALES)
              COLUMNS
                  ORTP VARCHAR2(6)    PATH 'orderType',
                  ORNO VARCHAR2(20)   PATH 'orderNumber',
                  CUNO VARCHAR2(20)   PATH 'customerNumber',
                  CUOR VARCHAR2(40)   PATH 'customerOrderReference',
                  BANO VARCHAR2(40)   PATH 'batchNumber',
                  DLIX NUMBER(11)     PATH 'deliveryNoteNumber',
                  ELNO VARCHAR2(16)   PATH 'elementNumber',
                  PONR NUMBER(5)      PATH 'orderLineNumber',
                  YREF VARCHAR2(60)   PATH 'creditNoteOrderNumber',
                  OREF VARCHAR2(60)   PATH 'orderNumberCreditNote',
                  CNNO VARCHAR2(20)   PATH 'creditNoteNumber',
                  CNIV NUMBER(9)      PATH 'creditNoteInvoiceNumber',
                  IVNO NUMBER(9)      PATH 'invoiceNumber',
                  ITCL VARCHAR2(10)   PATH 'itemClassification',
                  ITNO VARCHAR2(30)   PATH 'itemMaterialNumber',
                  IVQT NUMBER(15)     PATH 'invoicedQuantity',
                  IVDT NUMBER(8)      PATH 'invoiceDate'
          ) xt )
      LOOP
          INSERT INTO DE_SALES (ORTP,ORNO,CUNO,CUOR,BANO,DLIX,ELNO,PONR,YREF,OREF,CNNO,CNIV,IVNO,ITCL,ITNO,IVQT,IVDT,TRANSFER)
          VALUES (record_in_xml.ORTP,
              record_in_xml.ORNO,
              record_in_xml.CUNO,
              record_in_xml.CUOR,
              record_in_xml.BANO,
              record_in_xml.DLIX,
              record_in_xml.ELNO,
              record_in_xml.PONR,
              record_in_xml.YREF,
              record_in_xml.OREF,
              record_in_xml.CNNO,
              record_in_xml.CNIV,
              record_in_xml.IVNO,
              record_in_xml.ITCL,
              record_in_xml.ITNO,
              record_in_xml.IVQT,
              record_in_xml.IVDT,
              SYSDATE);
      END LOOP;
      COMMIT;        
  END;
  
  SELECT * FROM DE_SALES;
  DELETE FROM DE_SALES;
  
  begin 
  dortosales('&lt;?xml version="1.0" encoding="ISO-8859-1"?&gt;
  &lt;dortoSalesInputIF&gt;
      &lt;order&gt;
          &lt;orderType&gt;DO1&lt;/orderType&gt;
          &lt;orderNumber&gt;1304315&lt;/orderNumber&gt;
          &lt;customerNumber&gt;5104081&lt;/customerNumber&gt;
          &lt;customerOrderReference&gt;&lt;/customerOrderReference&gt;
          &lt;batchNumber&gt;&lt;/batchNumber&gt;
          &lt;deliveryNoteNumber&gt;8337697&lt;/deliveryNoteNumber&gt;
          &lt;elementNumber&gt;&lt;/elementNumber&gt;
          &lt;orderLineNumber&gt;2&lt;/orderLineNumber&gt;
          &lt;creditNoteOrderNumber&gt;617&lt;/creditNoteOrderNumber&gt;
          &lt;orderNumberCreditNote&gt;59237&lt;/orderNumberCreditNote&gt;
          &lt;creditNoteNumber&gt;&lt;/creditNoteNumber&gt;
          &lt;creditNoteInvoiceNumber&gt;&lt;/creditNoteInvoiceNumber&gt;
          &lt;invoiceNumber&gt;31615915&lt;/invoiceNumber&gt;
          &lt;itemClassification&gt;TRA&lt;/itemClassification&gt;
          &lt;itemMaterialNumber&gt;100000101&lt;/itemMaterialNumber&gt;
          &lt;invoicedQuantity&gt;1&lt;/invoicedQuantity&gt;
          &lt;invoiceDate&gt;20160531&lt;/invoiceDate&gt;
      &lt;/order&gt;
      &lt;order&gt;
          &lt;orderType&gt;DO1&lt;/orderType&gt;
          &lt;orderNumber&gt;1304316&lt;/orderNumber&gt;
          &lt;customerNumber&gt;5104082&lt;/customerNumber&gt;
          &lt;customerOrderReference&gt;&lt;/customerOrderReference&gt;
          &lt;batchNumber&gt;&lt;/batchNumber&gt;
          &lt;deliveryNoteNumber&gt;8337698&lt;/deliveryNoteNumber&gt;
          &lt;elementNumber&gt;&lt;/elementNumber&gt;
          &lt;orderLineNumber&gt;2&lt;/orderLineNumber&gt;
          &lt;creditNoteOrderNumber&gt;618&lt;/creditNoteOrderNumber&gt;
          &lt;orderNumberCreditNote&gt;59238&lt;/orderNumberCreditNote&gt;
          &lt;creditNoteNumber&gt;&lt;/creditNoteNumber&gt;
          &lt;creditNoteInvoiceNumber&gt;&lt;/creditNoteInvoiceNumber&gt;
          &lt;invoiceNumber&gt;31615916&lt;/invoiceNumber&gt;
          &lt;itemClassification&gt;TRA&lt;/itemClassification&gt;
          &lt;itemMaterialNumber&gt;100000101&lt;/itemMaterialNumber&gt;
          &lt;invoicedQuantity&gt;1&lt;/invoicedQuantity&gt;
          &lt;invoiceDate&gt;20160531&lt;/invoiceDate&gt;
      &lt;/order&gt;
  &lt;/dortoSalesInputIF&gt;');
  end;
  </code></pre>