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.
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.
1
Dear friends, I have found this solution.
My tables are :
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.
I was going to start talking about CLOB, Xmltype and UTL_FILE... but 2 hours? The.o
– marcus
Pow guy now got till Monday, ahuheauehe
– Gustavo
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
– Gustavo