oracle plsql consume web service returning xsd schema

Adeel Aslam 0
Viewed 62

I am consuming a webservice which is returning the xsd schema as response,below is the request and response details

Request :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sec="http://track.smsaexpress.com/secom/">
   <soapenv:Header/>
   <soapenv:Body>
      <sec:getShipUpdates>
         <sec:rowId> 504634558</sec:rowId>
         <!--Optional:-->
         <sec:passKey>TestingkeyExample</sec:passKey>
      </sec:getShipUpdates>
   </soapenv:Body>
</soapenv:Envelope>

Response

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <getShipUpdatesResponse xmlns="http://track.smsaexpress.com/secom/">
         <getShipUpdatesResult>
            <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
               <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
                  <xs:complexType>
                     <xs:choice minOccurs="0" maxOccurs="unbounded">
                        <xs:element name="Tracking">
                           <xs:complexType>
                              <xs:sequence>
                                 <xs:element name="rowId" type="xs:long" minOccurs="0"/>
                                 <xs:element name="awbNo" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Date" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Activity" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Details" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Location" type="xs:string" minOccurs="0"/>
                              </xs:sequence>
                           </xs:complexType>
                        </xs:element>
                     </xs:choice>
                  </xs:complexType>
               </xs:element>
            </xs:schema>
            <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
               <NewDataSet xmlns="">
                  <Tracking diffgr:id="Tracking1" msdata:rowOrder="0">
                     <rowId>99438814</rowId>
                     <awbNo>290012097109</awbNo>
                     <Date>12 Nov 2017 15:47</Date>
                     <Activity>DATA RECEIVED</Activity>
                     <Details>Online Data Submitted</Details>
                     <Location>Riyadh</Location>
                  </Tracking>
                  <Tracking diffgr:id="Tracking2" msdata:rowOrder="1">
                     <rowId>99438812</rowId>
                     <awbNo>290012097092</awbNo>
                     <Date>12 Nov 2017 15:47</Date>
                     <Activity>DATA RECEIVED</Activity>
                     <Details>Online Data Submitted</Details>
                     <Location>Riyadh</Location>
                  </Tracking>
               </NewDataSet>
            </diffgr:diffgram>
         </getShipUpdatesResult>
      </getShipUpdatesResponse>
   </soap:Body>
</soap:Envelope>

Code :

create or replace function getSamsaShipUpdates(p_row_id NUMBER,p_key_flg varchar2) return varchar2 IS   
    l_status VARCHAR2(4000) DEFAULT NULL;
    l_xml       XMLTYPE;
    begin
    
                 
                
              l_envelope :=  '<?xml version="1.0" encoding="utf-8"?>
                                <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sec="http://track.smsaexpress.com/secom/">
                               <soapenv:Header/>
                               <soapenv:Body>
                                  <sec:getShipUpdates>
                                     <sec:rowId>'||p_row_id||'</sec:rowId>
                                     <!--Optional:-->
                                     <sec:passKey>'||p_key_flg||'</sec:passKey>
                                  </sec:getShipUpdates>
                               </soapenv:Body>
                            </soapenv:Envelope>';
                
                            
              l_xml := APEX_WEB_SERVICE.make_request(
                p_url      => 'http://track.smsaexpress.com/SECOM/SMSAwebServiceIntl.asmx',
                p_action   => 'http://track.smsaexpress.com/secom/SMSAWebserviceIntl/getShipUpdates',
                p_envelope => l_envelope
              );
             
            
                 l_status := APEX_WEB_SERVICE.parse_xml(
                    p_xml   => l_xml,
                    p_xpath => '//text()',
                    p_ns    => 'xmlns="http://track.smsaexpress.com/secom/SMSAWebserviceIntl"'
                  );    
    
    
    end getSamsaShipUpdates;

The above code i am trying to get response into Varchar variable however the response is more than 4000 records as this is a data set . Please provide any example how exactly we can parse the response into a table or can be looped through.

Update

I have tried by putting response into CLOB variable however i am getting empty response while running the code

Below is the code

DECLARE
   v_xml   CLOB;
BEGIN
   v_xml :=
      '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <getTrackingResponse xmlns="http://track.smsaexpress.com/secom/SMSAWebserviceIntl">
            <getTrackingResult>
                <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
                    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
                        <xs:complexType>
                            <xs:choice minOccurs="0" maxOccurs="unbounded">
                                <xs:element name="Tracking">
                                    <xs:complexType>
                                        <xs:sequence>
                                            <xs:element name="awbno" type="xs:string" minOccurs="0" />
                                            <xs:element name="Date" type="xs:string" minOccurs="0" />
                                            <xs:element name="Activity" type="xs:string" minOccurs="0" />
                                            <xs:element name="Details" type="xs:string" minOccurs="0" />
                                            <xs:element name="Location" type="xs:string" minOccurs="0" />
                                        </xs:sequence>
                                    </xs:complexType>
                                </xs:element>
                            </xs:choice>
                        </xs:complexType>
                    </xs:element>
                </xs:schema>
                <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
                    <NewDataSet xmlns="">
                        <Tracking diffgr:id="Tracking1" msdata:rowOrder="0">
                            <awbno>290340366704</awbno>
                            <Date>19 Sep 2022 13:12</Date>
                            <Activity>PROOF OF DELIVERY CAPTURED</Activity>
                            <Details>Received By ::  zainb  at Sep 19 2022  1:12PM</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking2" msdata:rowOrder="1">
                            <awbno>290340366704</awbno>
                            <Date>19 Sep 2022 09:12</Date>
                            <Activity>OUT FOR DELIVERY</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking3" msdata:rowOrder="2">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 22:34</Date>
                            <Activity>DEPARTED HUB FACILITY</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking4" msdata:rowOrder="3">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 22:30</Date>
                            <Activity>ARRIVED HUB FACILITY</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking5" msdata:rowOrder="4">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 18:58</Date>
                            <Activity>DEPARTED FORM ORIGIN</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking6" msdata:rowOrder="5">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 16:41</Date>
                            <Activity>COLLECTED FROM RETAIL</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking7" msdata:rowOrder="6">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 15:09</Date>
                            <Activity>PICKED UP</Activity>
                            <Details>Dhahran</Details>
                            <Location>Dhahran</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking8" msdata:rowOrder="7">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 14:20</Date>
                            <Activity>DATA RECEIVED</Activity>
                            <Details>Online Data Submitted</Details>
                            <Location>NA</Location>
                        </Tracking>
                    </NewDataSet>
                </diffgr:diffgram>
            </getTrackingResult>
        </getTrackingResponse>
    </soap:Body>
</soap:Envelope>';

   FOR c  IN (SELECT awbno
              FROM XMLTABLE (
                      xmlnamespaces ('urn:schemas-microsoft-com:xml-msdata' as "msdata", 
                                     'urn:schemas-microsoft-com:xml-diffgram-v1' as "diffgr",
                         DEFAULT 'http://track.smsaexpress.com/secom/SMSAWebserviceIntl',
                         'http://schemas.xmlsoap.org/soap/envelope/' AS "soap"),
                      'soap:Envelope/soap:Body/getTrackingResponse/getTrackingResult/diffgr:diffgram/NewDataSet/Tracking/*'
                      PASSING xmltype (v_xml)
                      COLUMNS awbno VARCHAR (100) PATH './awbno'))
   LOOP
      DBMS_OUTPUT.put_line (c.awbno);
   END LOOP;
END;
oracle-apex plsql oracle database
1 Answers

You should use CLOB datatype for respose over 4000 characters. Declare variable of type CLOB and read your response into it. Below is pl/sql block (nested inside my main block) as an example that I use to do it...

Begin
    Loop
      UTL_HTTP.READ_TEXT(mResp, mBuff, 32000);
      DBMS_LOB.WriteAppend(mRetClob, Length(mBuff), mBuff);
    End Loop;
Exception
    WHEN UTL_HTTP.END_OF_BODY THEN
        UTL_HTTP.END_RESPONSE(mResp);
End;

... where mRet is my return variable (CLOB type), mResp is my response variable (UTL_HTTP.resp type) and mBuff is my buffer variable (VarChar2(32000) type). I use the DBMS_LOB.WriteAppend() function to write the data into LOB. In short - this code is reading the chunks of the response and appends it to the CLOB. PL/SQL can handle VarChar2 variiables to the length of 32767.
Next, to read the specific part from CLOB you can use a function like this ...

FUNCTION Get_SOAP_PartCLOB(mCLOB IN CLOB, mTag IN VARCHAR2) RETURN CLOB 
    IS
BEGIN
    Declare
        wrkCLOB      CLOB;
        myStart      NUMBER(10) := 0;
        myEnd        NUMBER(10) := 0;
        myLength     NUMBER(10) := 0;
        toErase      NUMBER(10) := 0;
    Begin
        DBMS_LOB.CreateTemporary(wrkCLOB, True);
        DBMS_LOB.COPY(wrkCLOB, mCLOB, DBMS_LOB.GETLENGTH(mCLOB));
        --
        myStart := DBMS_LOB.InStr(wrkCLOB, mTag, 1, 1) + Length(mTag) - 1;
        myEnd := DBMS_LOB.InStr(wrkCLOB, SubStr(mTag, 1, 1) || '/' || SubStr(mTag, 2));
        myLength := DBMS_LOB.GetLength(wrkCLOB);
        
        DBMS_LOB.ERASE(wrkCLOB, myStart, 1);
        toErase := myLength - myEnd + 1;

        DBMS_LOB.ERASE(wrkCLOB, toErase, myEnd);
        
        wrkCLOB := REPLACE(wrkCLOB, ' ', '');

        RETURN(wrkCLOB);
    End;
END Get_SOAP_PartCLOB;

I hope that you could adjust some of the above code to get your task done. Regards...
P.S. You asked about writing the response into a table. You should create a column of type CLOB in your table and then you can insert/update that column with the data collected in CLOB variable (mRet) above just like you do with any other column.
Example of selecting the data from CLOB column using XMLTABLE function:

SELECT DISTINCT 
        SubStr(xt1.INV_ID, 1, 27) "INV_ID", 
        xt1.MARK "MARK", 
        xt1.Code "CODE", 
        xt1.Goods "GOODS"
FROM    XML_ERN_MAIN x,         -- database table containing CLOB column with SOAP envelope response xml data
        XMLTABLE(
XMLNAMESPACES       -- declare Namespaces from the SOAP envelope response
    ( 
        'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' As "cbc", 
        'urn:oasis:names:specification:ubl:schema:xsd:SignatureAggregateComponents-2' As "sac",  
        'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' As "ext", 
        'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' As "cac", 
        'urn:oasis:names:specification:ubl:schema:xsd:CommonSignatureComponents-2' As "sig", 
        'urn:invoice:hr:schema:xsd:CommonExtensionComponents-1' As "exthr", 
        'http://www.w3.org/2001/XMLSchema-instance' As "xsi",  
        default 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'
    ),
       '/Invoice/cac:InvoiceLine'
            PASSING                 -- x.XML is CLOB type column in table XML_ERN_MAIN it can be a PL/SQL variable of type CLOB
                XMLTYPE(x.XML)      -- You have to convert the soap envelope response to XMLTYPE
                        COLUMNS 
                        INV_ID        VARCHAR2(20) PATH '/Invoice/../cbc:ID',
                        MARK          NUMBER(20)  PATH 'cbc:ID',
                        Code          VARCHAR2(10) PATH 'cbc:InvoicedQuantity/@unitCode',
                        Goods         VARCHAR2(50) PATH 'cac:Item/cbc:Name'
         ) xt1
WHERE x.MAIN_ID = 'SKD-ERN-1'
--  R e s u l t
--  INV_ID                     MARK CODE       GOODS               
--  -------------------- ---------- ---------- -------------------
--  5708-2-1                      1 T          FERTILIZERS 23876  
--  5708-2-1                      2 T          ADDITIVES-9879876

Update
with your now full working code:

SET SERVEROUTPUT ON
DECLARE
   v_xml   CLOB :=
      '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <getTrackingResponse xmlns="http://track.smsaexpress.com/secom/SMSAWebserviceIntl">
            <getTrackingResult>
                <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
                    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
                        <xs:complexType>
                            <xs:choice minOccurs="0" maxOccurs="unbounded">
                                <xs:element name="Tracking">
                                    <xs:complexType>
                                        <xs:sequence>
                                            <xs:element name="awbno" type="xs:string" minOccurs="0" />
                                            <xs:element name="Date" type="xs:string" minOccurs="0" />
                                            <xs:element name="Activity" type="xs:string" minOccurs="0" />
                                            <xs:element name="Details" type="xs:string" minOccurs="0" />
                                            <xs:element name="Location" type="xs:string" minOccurs="0" />
                                        </xs:sequence>
                                    </xs:complexType>
                                </xs:element>
                            </xs:choice>
                        </xs:complexType>
                    </xs:element>
                </xs:schema>
                <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
                    <NewDataSet xmlns="">
                        <Tracking diffgr:id="Tracking1" msdata:rowOrder="0">
                            <awbno>290340366704</awbno>
                            <Date>19 Sep 2022 13:12</Date>
                            <Activity>PROOF OF DELIVERY CAPTURED</Activity>
                            <Details>Received By ::  zainb  at Sep 19 2022  1:12PM</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking2" msdata:rowOrder="1">
                            <awbno>290340366704</awbno>
                            <Date>19 Sep 2022 09:12</Date>
                            <Activity>OUT FOR DELIVERY</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking3" msdata:rowOrder="2">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 22:34</Date>
                            <Activity>DEPARTED HUB FACILITY</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking4" msdata:rowOrder="3">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 22:30</Date>
                            <Activity>ARRIVED HUB FACILITY</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking5" msdata:rowOrder="4">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 18:58</Date>
                            <Activity>DEPARTED FORM ORIGIN</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking6" msdata:rowOrder="5">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 16:41</Date>
                            <Activity>COLLECTED FROM RETAIL</Activity>
                            <Details>Dammam</Details>
                            <Location>Dammam</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking7" msdata:rowOrder="6">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 15:09</Date>
                            <Activity>PICKED UP</Activity>
                            <Details>Dhahran</Details>
                            <Location>Dhahran</Location>
                        </Tracking>
                        <Tracking diffgr:id="Tracking8" msdata:rowOrder="7">
                            <awbno>290340366704</awbno>
                            <Date>18 Sep 2022 14:20</Date>
                            <Activity>DATA RECEIVED</Activity>
                            <Details>Online Data Submitted</Details>
                            <Location>NA</Location>
                        </Tracking>
                    </NewDataSet>
                </diffgr:diffgram>
            </getTrackingResult>
        </getTrackingResponse>
    </soap:Body>
</soap:Envelope>';
CURSOR c IS
    SELECT  AWBNO
    FROM     XMLTABLE ('//NewDataSet/Tracking'
             PASSING xmltype(v_xml)
                  COLUMNS AWBNO VARCHAR (100) PATH './awbno');
cSet   c%ROWTYPE;
BEGIN
   OPEN c;
   LOOP
      FETCH c INTO cSet;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.put_line (cSet.AWBNO);
   END LOOP;
   CLOSE c;
END;

I finaly get some time to look at your data closer and above code works ok with pl/sql CLOB variable passed to XMLTABLE() - here is the result:

/*
anonymous block completed
290340366704
290340366704
290340366704
290340366704
290340366704
290340366704
290340366704
290340366704
*/

You had the problem because your data area explicitely excludes namespaces

<NewDataSet xmlns="">
d r0
Related