Some problem with web services and pl-sql

[Eremita]

Hi Tim,

now this web service works fine with my functions :

Code: Select all

-- First function without parameter
declare
    l_env t_soap_envelope;
    l_xml xmltype;
    fitt  xmltype;
    l_val varchar2(4000);
    l_start_date date;
begin

    l_env := t_soap_envelope ('http://www.webservicex.net', 'geoipservice.asmx', 'GetGeoIPContext', 'xmlns="http://www.webservicex.net/"');
    --l_env.add_param ('IPAddress', '10.1.1.138');
   
    l_start_date := sysdate;
    l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
   
    -- flex_ws_util.get_value() in questo caso non ci serve, ma:
    l_val := flex_ws_util.get_value (l_xml, 'GetGeoIPContextResponse', null, 'error');
   
    --flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
    --    p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
   
    l_val := substr(l_xml.getClobVal(),39,length(l_xml.getClobVal()));
    dbms_output.put_line('XML DI RISPOSTA: ');
    dbms_output.put_line(l_val);
end;


Code: Select all

--Second function with parameter
declare
    l_env t_soap_envelope;
    l_xml xmltype;
    fitt  xmltype;
    l_val varchar2(4000);
    l_start_date date;
begin

    l_env := t_soap_envelope ('http://www.webservicex.net', 'geoipservice.asmx', 'GetGeoIP', 'xmlns="http://www.webservicex.net/"');
    l_env.add_param ('IPAddress', '10.1.1.138');
   
    l_start_date := sysdate;
    l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
   
    -- flex_ws_util.get_value() in questo caso non ci serve, ma:
    l_val := flex_ws_util.get_value (l_xml, 'GetGeoIPResponse', null, 'error');
   
    --flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
    --    p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
   
    l_val := substr(l_xml.getClobVal(),39,length(l_xml.getClobVal()));
    dbms_output.put_line('XML DI RISPOSTA: ');
    dbms_output.put_line(l_val);
end;


though the response is:

Code: Select all

<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>
    <GetGeoIPContextResponse xmlns="http://www.webservicex.net/">
      <GetGeoIPContextResult>
        <ReturnCode>1</ReturnCode>
        <IP>my ip</IP>
        <ReturnCodeDetails>Success</ReturnCodeDetails>
        <CountryName>European Union</CountryName>
        <CountryCode>EU</CountryCode>
      </GetGeoIPContextResult>
    </GetGeoIPContextResponse>
  </soap:Body>
</soap:Envelope>


How to do for extract the values??
I tested some solutions:
- https://forums.oracle.com/forums/thread ... dID=387277
and
- http://asitha.wordpress.com/2009/09/15/xmltype/

without success

For the complex types... i wait you for some suggestions!!
Thanks for your patience...

Cheers

[Tim...]

Hi.

There are several ways to get the data from the XML. You can see some examples here:

http://www.oracle-base.com/articles/9i/ ... nts-9i.php

If it's in an XMLTYPE, you can use the extract method shown here.

http://www.oracle-base.com/articles/9i/ ... tatype.php

Cheers

Tim...

[Eremita]

Hi,

excellent!!
I solved in this way:

Code: Select all

declare
    l_env t_soap_envelope;
    l_xml xmltype;
    fitt  xmltype;
    l_val varchar2(4000);
    l_start_date date;
   
    mio_xml   XMLTYPE;
    mio_doc   CLOB;
    info1      VARCHAR2(4000);
    info2      VARCHAR2(4000);
    info3      VARCHAR2(4000); 
begin

    l_env := t_soap_envelope ('http://www.webservicex.net', 'geoipservice.asmx', 'GetGeoIP', 'xmlns="http://www.webservicex.net/"');
    l_env.add_param ('IPAddress', '10.1.1.138');
   
    l_start_date := sysdate;
    l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
   
    -- flex_ws_util.get_value() in questo caso non ci serve, ma:
    l_val := flex_ws_util.get_value (l_xml, 'GetGeoIPResponse', null, 'error');
   
    --flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
    --    p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
   
    l_val := substr(l_xml.getClobVal(),39,length(l_xml.getClobVal()));
    dbms_output.put_line('XML DI RISPOSTA: ');
    --dbms_output.put_line(l_val);
   
    -- per estrarre i dati
    l_val := replace(l_val,':Envelope','');
    l_val := replace(l_val,'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"','');
    l_val := replace(l_val,'xmlns="http://www.webservicex.net/"','');
    l_val := replace(l_val,':Body','');
    dbms_output.put_line(l_val);
    mio_doc := l_val;
    mio_xml := sys.xmltype.createXML(mio_doc);
   
    SELECT extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/IP/text()').getStringVal() into info1
    FROM   dual;
    SELECT extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/CountryName/text()').getStringVal() into info2
    FROM   dual;
    SELECT extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/CountryCode/text()').getStringVal() into info3
    FROM   dual;
    --WHERE  existsNode(mio_xml,'/soap:Envelope/soap:Body/GetGeoIPResult/CountryCode')  = 1;
   
    /*
    -- o anche
    SELECT extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/IP/text()').getStringVal(),
           extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/CountryName/text()').getStringVal(),
           extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/CountryCode/text()').getStringVal()
           into info1, info2, info3
    FROM   dual;
    */
    dbms_output.put_line('IP: ' || info1);
    dbms_output.put_line('CountryName: ' || info2);
    dbms_output.put_line('CountryCode: ' || info3);
end;


Thanks much!!

Cheers

[Tim...]