PL/SQL: XML Parsing CyberSource Response Example

 

PL/SQL: XML Parsing CyberSource Response Example

 

Business Need:

Just an example of XML Parsing within PL/SQL.

This can be used to parse any XML within PL/SQL. Make sure APEX JSON is installed in database.

Note: We are using Oracle 12.1.0.0 database version. Some objects and parser are not available in older versions of Oracle database.

 

Program / PL/SQL Script:

set serveroutput on;
     -- Insert data into staging table
    DECLARE
             xAuthRespXML XMLType;
             V_AUTH_RESP_XML VARCHAR2(5000);
             sAuthResponseNameSpace  VARCHAR2(5000);
             v_item_count NUMBER;
       BEGIN
 
-- Substitute your XML Text here
V_AUTH_RESP_XML := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
       <soap:Header>
             <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
                    <wsu:Timestamp xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="Timestamp-1489510174">
                           <wsu:Created>2017-07-23T04:27:08.680Z</wsu:Created>
                    </wsu:Timestamp>
             </wsse:Security>
       </soap:Header>
       <soap:Body>
             <c:replyMessage xmlns:c="urn:schemas-cybersource-com:transaction-data-1.165">
                    <c:merchantReferenceCode>EbsONT122052844.27</c:merchantReferenceCode>
                    <c:requestID>5954784279986442603008</c:requestID>
                    <c:decision>ACCEPT</c:decision>
                    <c:reasonCode>100</c:reasonCode>
             <c:requestToken>Axj/7wSTQs87kNkwJAoAABsZLnsZc6HPnObVOMojhEbUIoBURwiNqEVpBK6PcDMhk0ky9GLDX7uBOTQs87kNkwJAoAAAXAhY</c:requestToken>
                    <c:purchaseTotals>
                           <c:currency>USD</c:currency>
                    </c:purchaseTotals>
                    <c:ccAuthReply>
                           <c:reasonCode>100</c:reasonCode>
                           <c:amount>1907.11</c:amount>
                           <c:authorizationCode>831000</c:authorizationCode>
                           <c:avsCode>Y</c:avsCode>
                           <c:avsCodeRaw>Y</c:avsCodeRaw>
                           <c:authorizedDateTime>2017-07-23T04:27:08Z</c:authorizedDateTime>
                           <c:processorResponse>00</c:processorResponse>
                           <c:reconciliationID>KO1KNCON9ZSF</c:reconciliationID>
                    <c:authRecord>0110322000000E10000200000000000019071107230427080517684B4F314B4E434F4E395A53463833313030303030000159004400103232415050524F56414C0022313457303136313530373033383032303934473036340006564943524120</c:authRecord>
                    <c:paymentNetworkTransactionID>016150703802094</c:paymentNetworkTransactionID>
                    </c:ccAuthReply>
                    <c:decisionEarlyReply>
                           <c:reasonCode>100</c:reasonCode>
                           <c:rcode>1</c:rcode>
                           <c:activeProfileReply/>
                    </c:decisionEarlyReply>
                    <c:card>
                           <c:cardType>001</c:cardType>
                    </c:card>
                    <c:acquirerMerchantNumber>000225130042990</c:acquirerMerchantNumber>
                    <c:pos>
                           <c:terminalID>06011497</c:terminalID>
                    </c:pos>
             </c:replyMessage>
       </soap:Body>
</soap:Envelope>';
 
               dbms_output.put_line('Before FOR loop through Customer XML');
 
               xAuthRespXML :=  XMLType(V_AUTH_RESP_XML);
               sAuthResponseNameSpace := ' xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:c="urn:schemas-cybersource-com:transaction-data-1.141"';
 
               v_item_count := 0;
               FOR r IN (
                    SELECT
                           ExtractValue(Value(p),'/c:replyMessage/c:merchantReferenceCode/text()', sAuthResponseNameSpace) as merchantReferenceCode
                    FROM   TABLE(XMLSequence(Extract(xAuthRespXML,'/soap:Envelope/soap:Body/c:replyMessage', sAuthResponseNameSpace))) p
                    ) LOOP
 
                    v_item_count := v_item_count + 1;
                    dbms_output.put_line('CustomerXML Loop Number: ' || v_item_count || '; merchantReferenceCode:' || r.merchantReferenceCode);
 
 
 
               END LOOP;
               dbms_output.put_line('After FOR loop through Customer XML');
 
 
 
 
 
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Exception while inserting Customer Data to Staging table; SQLCODE:' || SQLCODE || '; SQLERRM:' || SQLERRM);
    END;
/
 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

XML, PL/SQL, Parser, APEX JSON, Patch