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;
CodePudding user response：
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="">