Oracle Store Procedure reading XML

Dear friends, I found this solution.

My tables are:

• Temporary "INFO_XML"
• Final Table: "TAB_XML"

I have only 10 days in the Oracle world, I hope I was not very wrong, but I learned that a delete first of all to clear the table, followed by a commit to what action is taken, a cursor is required to take the path, an insert to enter the values, extract value to get an external file from the bank and put inside the my tables.

create or replace PACKAGE BODY PACK_GWP0009
AS
  -- PARAMETROS PARA TESTE : CARGA_CSV , funcionarios.xml
  CURSOR C1
  IS
    SELECT NVL (MAX(Id),0)+1 AS ID FROM INFO_XML;
  CURSOR C2  IS  
  SELECT 
     EXTRACTVALUE (value(func), '/Transaction/Order/MerchantID') MerchantID
    ,EXTRACTVALUE (value(func), '/Transaction/Order/RequestID') RequestID
    ,EXTRACTVALUE (value(func), '/Transaction/Order/TransactionDate') TransactionDate
    ,EXTRACTVALUE (value(func), '/Transaction/Order/MerchantReferenceNumber') MerchantReferenceNumber   
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/FirstName') FirstName  
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/LastName') LastName  
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/Address1') Address1
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/City') City
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/State') State
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/PostalCode') PostalCode
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/Country') Country
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/Phone') Phone
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/Email') Email
    ,EXTRACTVALUE (value(func), '/Transaction/Customer/BillTo/CustomerID') CustomerID
    ,EXTRACTVALUE (value(func), '/Transaction/Payment/CardType') CardType
    ,EXTRACTVALUE (value(func), '/Transaction/Payment/CardScheme') CardScheme
    ,EXTRACTVALUE (value(func), '/Transaction/Payment/CardIssuer') CardIssuer
    ,EXTRACTVALUE (value(func), '/Transaction/Payment/CardBIN') CardBIN
    ,EXTRACTVALUE (value(func), '/Transaction/Payment/CardBINCountry') CardBINCountry
    ,EXTRACTVALUE (value(func), '/Transaction/Payment/OrderAmount') OrderAmount
    ,EXTRACTVALUE (value(func), '/Transaction/Payment/OrderCurrency') OrderCurrency
    ,EXTRACTVALUE (value(func), '/Transaction/Payment/LocalOrderAmount') LocalOrderAmount
    ,EXTRACTVALUE (value(func), '/Transaction/AFSInformation/Score') Score
    ,EXTRACTVALUE (value(func), '/Transaction/AFSInformation/Factors') Factors
    ,EXTRACTVALUE (value(func), '/Transaction/AFSInformation/InfoCodeString') InfoCodeString
    ,EXTRACTVALUE (value(func), '/Transaction/AFSInformation/IPInformation/IPAddress') IPAddress
    ,EXTRACTVALUE (value(func), '/Transaction/AFSInformation/IPInformation/IPRoutingMethod') IPRoutingMethod
    ,EXTRACTVALUE (value(func), '/Transaction/AFSInformation/IPInformation/IPCountry') IPCountry
    ,EXTRACTVALUE (value(func), '/Transaction/AFSInformation/IPInformation/IPState') IPState
    ,EXTRACTVALUE (value(func), '/Transaction/AFSInformation/IPInformation/IPCity') IPCity
    ,EXTRACTVALUE (value(func), '/Transaction/TravelData/CompleteRoute') CompleteRoute
    ,EXTRACTVALUE (value(func), '/Transaction/TravelData/DepartureDateTime') DepartureDateTime
    ,EXTRACTVALUE (value(func), '/Transaction/TravelData/JourneyType') JourneyType
    ,EXTRACTVALUE (value(func), '/Transaction/TravelData/NumberOfPassengers') NumberOfPassengers
   -- ,EXTRACTVALUE (value(func), '/Transaction/TravelData/Passenger/FirstName') FirstName
   -- ,EXTRACTVALUE (value(func), '/Transaction/TravelData/Passenger/LastName') LastName
   -- ,EXTRACTVALUE (value(func), '/Transaction/TravelData/Passenger/PassengerType') PassengerType
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field1') Field1
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field2') Field2
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field3') Field3
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field4') Field4
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field5') Field5
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field6') Field6
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field7') Field7
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field9') Field9
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field10') Field10
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field11') Field11
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field12') Field12
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field14') Field14
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field15') Field15
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field16') Field16
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field17') Field17
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field21') Field21
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field25') Field25
    ,EXTRACTVALUE (value(func), '/Transaction/MerchantDefinedData/Field26') Field26
    ,EXTRACTVALUE (value(func), '/Transaction/CaseManagement/Profile/Active/Name') Name
    ,EXTRACTVALUE (value(func), '/Transaction/CaseManagement/Profile/Active/Decision') Decision
    ,EXTRACTVALUE (value(func), '/Transaction/CaseManagement/Profile/Active/NumberOfRules') NumberOfRules
    ,EXTRACTVALUE (value(func), '/Transaction/DeviceFingerprint/Fingerprint') Fingerprint
    ,EXTRACTVALUE (value(func), '/Transaction/DeviceFingerprint/SmartID') SmartID
    ,EXTRACTVALUE (value(func), '/Transaction/DeviceFingerprint/SmartIDConfidenceLevel') SmartIDConfidenceLevel
    ,EXTRACTVALUE (value(func), '/Transaction/DeviceFingerprint/ScreenResolution') ScreenResolution
    ,EXTRACTVALUE (value(func), '/Transaction/DeviceFingerprint/TrueIPAddress/Address') Address
    ,EXTRACTVALUE (value(func), '/Transaction/DeviceFingerprint/TrueIPAddress/Country') Country
    ,EXTRACTVALUE (value(func), '/Transaction/DeviceFingerprint/TrueIPAddress/City') City
FROM INFO_XML, TABLE (XMLSEQUENCE(EXTRACT(conteudo, '/funcionarios/Transaction'))) func;

PROCEDURE load_xml(
    p_id       IN NUMBER,
    p_filename IN VARCHAR2)
IS
  v_ID INFO_XML.ID%type;
  v_MERCHANTID TAB_XML.MERCHANTID%type;
  v_REQUESTID TAB_XML.REQUESTID%type;
  v_TRANSACTIONDATE TAB_XML.TRANSACTIONDATE%type;
  v_MERCHANTREFERENCENUMBER TAB_XML.MERCHANTREFERENCENUMBER%type;
  v_FIRSTNAME TAB_XML.FIRSTNAME%type;
  v_LASTNAME TAB_XML.LASTNAME%type;
  v_ADDRESS1 TAB_XML.ADDRESS1%type;
  v_CITY TAB_XML.CITY%type;
  v_STATE TAB_XML.STATE%type;
  v_POSTALCODE TAB_XML.POSTALCODE%type;
  v_COUNTRY TAB_XML.COUNTRY%type;
  v_PHONE TAB_XML.PHONE%type;
  v_EMAIL TAB_XML.EMAIL%type;
  v_CUSTOMERID TAB_XML.CUSTOMERID %type;
  v_CARDTYPE TAB_XML.CARDTYPE%type;
  v_CARDSCHEME TAB_XML.CARDSCHEME%type;
  v_CARDISSUER TAB_XML.CARDISSUER%type;
  v_CARDBIN TAB_XML.CARDBIN%type;
  v_CARDBINCOUNTRY TAB_XML.CARDBINCOUNTRY%type;
  v_ORDERAMOUNT TAB_XML.ORDERAMOUNT%type;
  v_ORDERCURRENCY TAB_XML.ORDERCURRENCY%type;
  v_LOCALORDERAMOUNT TAB_XML.LOCALORDERAMOUNT%type;
  v_SCORE TAB_XML.SCORE%type;
  v_FACTORS TAB_XML.FACTORS%type;
  v_INFOCODESTRING TAB_XML.INFOCODESTRING%type;
  v_IPINFORMATION TAB_XML.IPINFORMATION%type;
  v_IPADDRESS TAB_XML.IPADDRESS%type;
  v_IPROUTINGMETHOD TAB_XML.IPROUTINGMETHOD%type;
  v_IPCOUNTRY TAB_XML.IPCOUNTRY%type;
  v_IPSTATE TAB_XML.IPSTATE%type;
  v_IPCITY TAB_XML.IPCITY%type;
  v_COMPLETEROUTE TAB_XML.COMPLETEROUTE %type;
  v_DEPARTUREDATETIME TAB_XML.DEPARTUREDATETIME%type;
  v_JOURNEYTYPE TAB_XML.JOURNEYTYPE%type;
  v_NUMBEROFPASSENGERS TAB_XML.NUMBEROFPASSENGERS%type;
  v_PASSENGER TAB_XML.PASSENGER%type;
  v_FIRSTNAME_1 TAB_XML.FIRSTNAME_1%type;
  v_LASTNAME_1 TAB_XML.LASTNAME_1%type;
  v_PASSENGERTYPE TAB_XML.PASSENGERTYPE%type;
  v_FIELD1 TAB_XML.FIELD1%type;
  v_FIELD2 TAB_XML.FIELD2%type;
  v_FIELD3 TAB_XML.FIELD3%type;
  v_FIELD4 TAB_XML.FIELD4%type;
  v_FIELD5 TAB_XML.FIELD5%type;
  v_FIELD6 TAB_XML.FIELD6%type;
  v_FIELD7 TAB_XML.FIELD7%type;
  v_FIELD8 TAB_XML.FIELD8%type;
  v_FIELD9 TAB_XML.FIELD9%type;
  v_FIELD10 TAB_XML.FIELD10%type;
  v_FIELD11 TAB_XML.FIELD11%type;
  v_FIELD12 TAB_XML.FIELD12%type;
  v_FIELD13 TAB_XML.FIELD13%type;
  v_FIELD14 TAB_XML.FIELD14%type;
  v_FIELD15 TAB_XML.FIELD15%type;
  v_FIELD16 TAB_XML.FIELD16%type;
  v_FIELD17 TAB_XML.FIELD17%type;
  v_FIELD18 TAB_XML.FIELD18%type;
  v_FIELD19 TAB_XML.FIELD19%type;
  v_FIELD20 TAB_XML.FIELD20%type;
  v_FIELD21 TAB_XML.FIELD21%type;
  v_FIELD22 TAB_XML.FIELD22%type;
  v_FIELD23 TAB_XML.FIELD23%type;
  v_FIELD24 TAB_XML.FIELD24%type;
  v_FIELD25 TAB_XML.FIELD25%type;
  v_FIELD26 TAB_XML.FIELD26%type;
  v_FIELD27 TAB_XML.FIELD27%type;
  v_FIELD28 TAB_XML.FIELD28%type;
  v_PROFILE TAB_XML.PROFILE%type;
  v_ACTIVE TAB_XML.ACTIVE%type;
  v_NAME TAB_XML.NAME%type;
  v_DECISION TAB_XML.DECISION%type;
  v_NUMBEROFRULES TAB_XML.NUMBEROFRULES%type;
  v_NAME_1 TAB_XML.NAME_1%type;
  v_DECISION_1 TAB_XML.DECISION_1%type;
  v_FINGERPRINT TAB_XML.FINGERPRINT%type;
  v_SMARTID TAB_XML.SMARTID%type;
  v_SMARTIDCONFIDENCELEVEL TAB_XML.SMARTIDCONFIDENCELEVEL%type;
  v_SCREENRESOLUTION TAB_XML.SCREENRESOLUTION%type;
  v_TRUEIPADDRESS TAB_XML.TRUEIPADDRESS%type;
  v_ADDRESS TAB_XML.ADDRESS%type;
  v_COUNTRY_1 TAB_XML.COUNTRY_1%type;
  v_CITY_1 TAB_XML.CITY_1%type;
  v_TIMEONPAGE TAB_XML.TIMEONPAGE%type;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO v_ID;
    EXIT
  WHEN C1%NOTFOUND;
  END LOOP;
  CLOSE C1;
  v_ID:=1;

  -- INSERE NA TABELA INFO_XML O .XML INTEIRO EM UMA COLUNA XMLTYPE
  DELETE
  FROM INFO_XML;
  COMMIT;
  INSERT
  INTO INFO_XML
    (
      ID,
      DATA_ALTERACAO,
      CONTEUDO
    )
    VALUES
    (
      v_ID,
      SYSDATE,
      XMLTYPE(BFILENAME('CARGA_CSV', 'fornecedores.xml'), NLS_CHARSET_ID('AL32UTF8'))
    );
  COMMIT;

 -- EXCLUSÂO DA TAB_XML
 DELETE FROM TAB_XML;
 COMMIT;

  --INCLUSÃO NA TABELA TAB_XML
  OPEN C2;
  LOOP
    FETCH C2
    INTO v_MERCHANTID, v_REQUESTID, v_TRANSACTIONDATE, v_MERCHANTREFERENCENUMBER
    ,v_FIRSTNAME, v_LASTNAME, v_ADDRESS1, v_CITY, v_STATE, v_POSTALCODE
    ,v_COUNTRY, v_PHONE, v_EMAIL, v_CUSTOMERID, v_CARDTYPE, v_CARDSCHEME
    ,v_CARDISSUER, v_CARDBIN, v_CARDBINCOUNTRY, v_ORDERAMOUNT, v_ORDERCURRENCY
    ,v_LOCALORDERAMOUNT, v_SCORE, v_FACTORS, v_INFOCODESTRING, v_IPADDRESS
    ,v_IPROUTINGMETHOD, v_IPCOUNTRY, v_IPSTATE, v_IPCITY, v_COMPLETEROUTE
    ,v_DEPARTUREDATETIME, v_JOURNEYTYPE, v_NUMBEROFPASSENGERS, v_FIELD1, v_FIELD2
    ,v_FIELD3, v_FIELD4, v_FIELD5, v_FIELD6, v_FIELD7, v_FIELD9, v_FIELD10
    ,v_FIELD11, v_FIELD12, v_FIELD14, v_FIELD15, v_FIELD16, v_FIELD17, v_FIELD21
    ,v_FIELD25, v_FIELD26, v_NAME,v_DECISION, v_NUMBEROFRULES, v_FINGERPRINT
    ,v_SMARTID, v_SMARTIDCONFIDENCELEVEL, v_SCREENRESOLUTION, v_ADDRESS,v_COUNTRY_1
    ,v_CITY_1;

    INSERT INTO TAB_XML (MERCHANTID,REQUESTID,TRANSACTIONDATE,MERCHANTREFERENCENUMBER
    ,FIRSTNAME,LASTNAME,ADDRESS1,CITY,STATE,POSTALCODE,COUNTRY,PHONE,EMAIL,CUSTOMERID
    ,CARDTYPE,CARDSCHEME,CARDISSUER,CARDBIN,CARDBINCOUNTRY,ORDERAMOUNT,ORDERCURRENCY
    ,LOCALORDERAMOUNT,SCORE,FACTORS,INFOCODESTRING,IPADDRESS,IPROUTINGMETHOD,IPCOUNTRY
    ,IPSTATE,IPCITY,COMPLETEROUTE,DEPARTUREDATETIME,JOURNEYTYPE,NUMBEROFPASSENGERS
    ,FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7,FIELD9,FIELD10,FIELD11,FIELD12
    ,FIELD14,FIELD15,FIELD16,FIELD17,FIELD21,FIELD25,FIELD26,NAME,DECISION,NUMBEROFRULES
    ,FINGERPRINT,SMARTID,SMARTIDCONFIDENCELEVEL,SCREENRESOLUTION,ADDRESS,COUNTRY_1,CITY_1)
    VALUES (v_MERCHANTID, v_REQUESTID, v_TRANSACTIONDATE, v_MERCHANTREFERENCENUMBER
    ,v_FIRSTNAME, v_LASTNAME, v_ADDRESS1, v_CITY, v_STATE, v_POSTALCODE
    ,v_COUNTRY, v_PHONE, v_EMAIL, v_CUSTOMERID, v_CARDTYPE, v_CARDSCHEME
    ,v_CARDISSUER, v_CARDBIN, v_CARDBINCOUNTRY, v_ORDERAMOUNT, v_ORDERCURRENCY
    ,v_LOCALORDERAMOUNT, v_SCORE, v_FACTORS, v_INFOCODESTRING, v_IPADDRESS
    ,v_IPROUTINGMETHOD, v_IPCOUNTRY, v_IPSTATE, v_IPCITY, v_COMPLETEROUTE
    ,v_DEPARTUREDATETIME, v_JOURNEYTYPE, v_NUMBEROFPASSENGERS, v_FIELD1, v_FIELD2
    ,v_FIELD3, v_FIELD4, v_FIELD5, v_FIELD6, v_FIELD7, v_FIELD9, v_FIELD10
    ,v_FIELD11, v_FIELD12, v_FIELD14, v_FIELD15, v_FIELD16, v_FIELD17, v_FIELD21
    ,v_FIELD25, v_FIELD26, v_NAME,v_DECISION, v_NUMBEROFRULES, v_FINGERPRINT
    ,v_SMARTID, v_SMARTIDCONFIDENCELEVEL, v_SCREENRESOLUTION, v_ADDRESS,v_COUNTRY_1
    ,v_CITY_1);
  COMMIT;

  EXIT WHEN C2%NOTFOUND;
  END LOOP;
  CLOSE C2; 
  DBMS_OUTPUT.PUT_LINE ('PROCEDURE CRIADA COM SUCESSO !!!');
END load_xml;
END PACK_GWP0009;