Oracle Store Procedure reading XML

Asked

Viewed 1,458 times

0

I need to make a stored procedure read an XML file and write it to a table in my database, but I have no idea how to do this...

Can someone help me? I have 2 hours to deliver this.

xml oracle procedure

  • I was going to start talking about CLOB, Xmltype and UTL_FILE... but 2 hours? The.o

  • Pow guy now got till Monday, ahuheauehe

  • I have already created a directory, a table and I have an xml example that I made, now I need to make the application that reads the xml and writes to my table, if she reads the 10 fields she also reads 1000, I kept it to solve in this fds :T

1 answer

1

Dear friends, I have 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’m not too mistaken, but I learned that a delete before anything else to clear the table, followed by a commit for action to be taken, a cursor is required for it to make the path, a Insert to enter the values, Extract value to take an external file from the bank and put it inside 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;

Browser other questions tagged xml oracle procedure

You are not signed in. Login or sign up in order to post.