Removing Escape Characters from Soap Response

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Removing Escape Characters from Soap Response

Postby Srinivas » Fri May 18, 2012 2:23 pm

Hi Tim

I have a webservice which returns my soap response and the response is like this.

Code: Select all
<?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>
    <Bins_GetResponse xmlns="http://xx/">
      <Bins_GetResult xsi:type="xsd:string">&lt;?xml version=&quot;1.0&quot;?&gt;
&lt;Bins_GetResult xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot; RecordCount=&quot;2&quot;&gt;
  &lt;Bin xmlns=&quot;http://www.xyz.com/Bins_Get.xsd&quot;&gt;
    &lt;BinID&gt;1439592&lt;/BinID&gt;
    &lt;SerialNumber&gt;O200003041858&lt;/SerialNumber&gt;
    &lt;BinType&gt;ORGANIC&lt;/BinType&gt;
    &lt;BinSize&gt;240L&lt;/BinSize&gt;
    &lt;BinStatus&gt;IN SERVICE&lt;/BinStatus&gt;
    &lt;Barcode&gt;O200003041858&lt;/Barcode&gt;
    &lt;RFID /&gt;
    &lt;UPRN xsi:type=&quot;xsd:decimal&quot;&gt;200003041858&lt;/UPRN&gt;
    &lt;RecordStamp&gt;
      &lt;AddedBy&gt;bartec&lt;/AddedBy&gt;
      &lt;DateAdded&gt;2010-04-13T16:21:46&lt;/DateAdded&gt;
      &lt;Comments /&gt;
    &lt;/RecordStamp&gt;
  &lt;/Bin&gt;
  &lt;Bin xmlns=&quot;http://www.xyz.com/Bins_Get.xsd&quot;&gt;
    &lt;BinID&gt;1439593&lt;/BinID&gt;
    &lt;SerialNumber&gt;D200003041858&lt;/SerialNumber&gt;
    &lt;BinType&gt;RECYCLABLES&lt;/BinType&gt;
    &lt;BinSize&gt;240L&lt;/BinSize&gt;
    &lt;BinStatus&gt;IN SERVICE&lt;/BinStatus&gt;
    &lt;Barcode&gt;D200003041858&lt;/Barcode&gt;
    &lt;RFID /&gt;
    &lt;UPRN xsi:type=&quot;xsd:decimal&quot;&gt;200003041858&lt;/UPRN&gt;
    &lt;RecordStamp&gt;
      &lt;AddedBy&gt;bartec&lt;/AddedBy&gt;
      &lt;DateAdded&gt;2010-04-13T16:21:46&lt;/DateAdded&gt;
      &lt;Comments /&gt;
    &lt;/RecordStamp&gt;
  &lt;/Bin&gt;
  &lt;Errors xmlns=&quot;http://www.xyz.com/Bins_Get.xsd&quot;&gt;
    &lt;Error&gt;
      &lt;Result&gt;0&lt;/Result&gt;
      &lt;Message /&gt;
    &lt;/Error&gt;
  &lt;/Errors&gt;
&lt;/Bins_GetResult&gt;</Bins_GetResult>
    </Bins_GetResponse>
  </soap:Body>
</soap:Envelope>


When i put in an oracle table with xmltype column the escape chars &lt and &gt are stored instead of '>' and '<'

Code: Select all

RESP := XMLTYPE.CREATEXML (SOAP_RESPOND);
 
INSERT INTO XXHCC_STAGING_XML VALUES (XMLTYPE(SOAP_RESPOND), SYSDATE);



Before i put in my table i want the escape chars to be replaced with actual tags like '>' and '<' like a proper xml envelope.

Can you let me know how to go about it.

My environment is
--------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

Thanks

Sri
Srinivas
Senior Member
 
Posts: 185
Joined: Mon Nov 08, 2004 10:14 am
Location: UK

Re: Removing Escape Characters from Soap Response

Postby Tim... » Fri May 18, 2012 5:36 pm

Hi.

You could just replace them like this:

Code: Select all
SOAP_RESPOND := REPLACE(SOAP_RESPOND, CHR(38) || 'lt;', '<');
SOAP_RESPOND := REPLACE(SOAP_RESPOND, CHR(38) || 'gt;', '>');
SOAP_RESPOND := REPLACE(SOAP_RESPOND, CHR(38) || 'quot;', '"');
RESP := XMLTYPE.CREATEXML (SOAP_RESPOND);
 
INSERT INTO XXHCC_STAGING_XML VALUES (XMLTYPE(SOAP_RESPOND), SYSDATE);


*BUT* : You seem to have a full XML document as the payload. If you were to convert the escaped characters this would result in an invalid document. You can't have an XML definition with an XML document. It will result in this.

Code: Select all
error on line 5 at column 37: XML declaration allowed only at the start of the document


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Removing Escape Characters from Soap Response

Postby Srinivas » Tue May 22, 2012 1:38 pm

Hi Tim

I got it by using like this
Code: Select all
soap_respond :=  xmltype(soap_respond).extract( '/soap:Envelope/soap:Body/Streets_Events_GetResponse/Streets_Events_GetResult/text()'
                                    , 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/",
                                       xmlns="http://xx/"' ).getclobval();


and then using this function to insert into my table

Code: Select all

INSERT INTO XXHCC_STAGING_XML   VALUES ( xmltype(dbms_xmlgen.convert(soap_respond, dbms_xmlgen.ENTITY_DECODE)), sysdate );   
         COMMIT;


Sri
Srinivas
Senior Member
 
Posts: 185
Joined: Mon Nov 08, 2004 10:14 am
Location: UK

Re: Removing Escape Characters from Soap Response

Postby Tim... » Tue May 22, 2012 2:23 pm

Hi.

That's cool. You've removed the outer envelope, allowing you to convert the contents without getting the error I mentioned.

LOL. I completely forgot about dbms_xmlgen.convert. I use it all the time to escape text. Not sure I've ever used in reverse.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Removing Escape Characters from Soap Response

Postby Srinivas » Tue May 22, 2012 3:37 pm

Tim

Yeah, Removed the envelope.

Got another question for you, If my procedure returns xmltype, as my procedure would be used by other oracle packages, how do i display the output in sqlplus.

I hope you got me.

I have to declare anonymous pl/sql block with a variable of xmltype and then execute my procedure,

Something is wrong in the code, but right now cant think of it...

Code: Select all
declare
   result xmltype;
begin
       mypMYPROCSTREET_EVENTS_GET('19101705',:result);
       dbms_output.put_line result.getclobval();
end;


The above doesnt work, I cant figure out how do i display the output xml in TOAD or sqlplus

Can u let me know

Many thanks

S
Srinivas
Senior Member
 
Posts: 185
Joined: Mon Nov 08, 2004 10:14 am
Location: UK

Re: Removing Escape Characters from Soap Response

Postby Tim... » Tue May 22, 2012 4:25 pm

Hi.

When you say it doesn't work, what do you mean? It errors?

I think you mean,

Code: Select all
dbms_output.put_line(result.getclobval());



Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Removing Escape Characters from Soap Response

Postby Srinivas » Fri May 25, 2012 10:34 am

Tim

got it working, but i have question

when i get the SOAP Response I m doing

Code: Select all
  RESP := XMLTYPE.CREATEXML (SOAP_RESPOND);
  INSERT INTO XXHCC_BARTECSTAGING_XML VALUES (xmltype(SOAP_RESPOND), sysdate,'Street_Events_Get SOAP Response' );
  Commit;


and xml stored is like this

Code: Select all
<?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>
    <Streets_Events_GetResponse xmlns="http://xx/">
      <Streets_Events_GetResult xsi:type="xsd:string">&lt;?xml version=&quot;1.0&quot;?&gt;
&lt;Streets_Events_GetResult xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot; RecordCount=&quot;36&quot;&gt;
  &lt;Event xmlns=&quot;http://www.systems.com/Streets_Events_Get.xsd&quot;&gt;
    &lt;EventType&gt;STREET COMPLETED&lt;/EventType&gt;
    &lt;EventDate&gt;2011-09-22T08:01:03&lt;/EventDate&gt;
    &lt;USRN&gt;19101705&lt;/USRN&gt;
    &lt;EventLocation&gt;
      &lt;BNG Easting=&quot;0&quot; Northing=&quot;0&quot; /&gt;
      &lt;Metric Latitude=&quot;53.789210&quot; Longitude=&quot;-0.328330&quot; /&gt;
    &lt;/EventLocation&gt;
    &lt;DeviceName&gt;1976 - R5&lt;/DeviceName&gt;
    &lt;DeviceType&gt;Ranger&lt;/DeviceType&gt;
    &lt;SheetName&gt;R5-THU-BLUE-220911&lt;/SheetName&gt;
    &lt;BinTypes&gt;RECYCLABLES; &lt;/BinTypes&gt;
    &lt;RecordStamp&gt;
      &lt;AddedBy&gt;wc2010_hull_ranger&lt;/AddedBy&gt;
      &lt;DateAdded&gt;2011-09-22T08:03:52&lt;/DateAdded&gt;
      &lt;Comments /&gt;
    &lt;/RecordStamp&gt;
  &lt;/Event&gt;
  &lt;Event xmlns=&quot;http://www.systems.com/Streets_Events_Get.xsd&quot;&gt;
    &lt;EventType&gt;STREET COMPLETED&lt;/EventType&gt;
    &lt;EventDate&gt;2011-04-21T07:58:12&lt;/EventDate&gt;
    &lt;USRN&gt;19101705&lt;/USRN&gt;
    &lt;EventLocation&gt;
      &lt;BNG Easting=&quot;0&quot; Northing=&quot;0&quot; /&gt;
      &lt;Metric Latitude=&quot;53.789120&quot; Longitude=&quot;-0.328300&quot; /&gt;
    &lt;/EventLocation&gt;
    &lt;DeviceName&gt;1976 - R5&lt;/DeviceName&gt;
    &lt;DeviceType&gt;Ranger&lt;/DeviceType&gt;
    &lt;SheetName&gt;R5-THU-BLUE-210411&lt;/SheetName&gt;
    &lt;BinTypes&gt;RECYCLABLES; &lt;/BinTypes&gt;
    &lt;RecordStamp&gt;
      &lt;AddedBy&gt;wc2010_hull_ranger&lt;/AddedBy&gt;
      &lt;DateAdded&gt;2011-04-21T07:58:51&lt;/DateAdded&gt;
      &lt;Comments /&gt;
    &lt;/RecordStamp&gt;
  &lt;/Event&gt;
  &lt;Event xmlns=&quot;http://www.systems.com/Streets_Events_Get.xsd&quot;&gt;
    &lt;EventType&gt;STREET COMPLETED&lt;/EventType&gt;
    &lt;EventDate&gt;2011-04-14T07:41:00&lt;/EventDate&gt;
    &lt;USRN&gt;19101705&lt;/USRN&gt;
    &lt;EventLocation&gt;
      &lt;BNG Easting=&quot;0&quot; Northing=&quot;0&quot; /&gt;
      &lt;Metric Latitude=&quot;53.789240&quot; Longitude=&quot;-0.328260&quot; /&gt;
    &lt;/EventLocation&gt;
    &lt;DeviceName&gt;1976 - R5&lt;/DeviceName&gt;
    &lt;DeviceType&gt;Ranger&lt;/DeviceType&gt;
    &lt;SheetName&gt;R5-THU-BROWN-140411&lt;/SheetName&gt;
    &lt;BinTypes&gt;ORGANIC; &lt;/BinTypes&gt;
    &lt;RecordStamp&gt;
      &lt;AddedBy&gt;wc2010_hull_ranger&lt;/AddedBy&gt;
      &lt;DateAdded&gt;2011-04-14T07:41:59&lt;/DateAdded&gt;
      &lt;Comments /&gt;
    &lt;/RecordStamp&gt;
  &lt;/Event&gt;
  &lt;Event xmlns=&quot;http://www.systems.com/Streets_Events_Get.xsd&quot;&gt;
    &lt;EventType&gt;STREET COMPLETED&lt;/EventType&gt;
    &lt;EventDate&gt;2011-04-07T08:10:00&lt;/EventDate&gt;
    &lt;USRN&gt;19101705&lt;/USRN&gt;
    &lt;EventLocation&gt;
      &lt;BNG Easting=&quot;0&quot; Northing=&quot;0&quot; /&gt;
      &lt;Metric Latitude=&quot;53.789230&quot; Longitude=&quot;-0.328220&quot; /&gt;
    &lt;/EventLocation&gt;
    &lt;DeviceName&gt;1976 - R5&lt;/DeviceName&gt;
    &lt;DeviceType&gt;Ranger&lt;/DeviceType&gt;
    &lt;SheetName&gt;R5-THU-BLUE-070411&lt;/SheetName&gt;
    &lt;BinTypes&gt;RECYCLABLES; &lt;/BinTypes&gt;
    &lt;RecordStamp&gt;
      &lt;AddedBy&gt;wc2010_hull_ranger&lt;/AddedBy&gt;
      &lt;DateAdded&gt;2011-04-07T08:11:48&lt;/DateAdded&gt;
      &lt;Comments /&gt;
    &lt;/RecordStamp&gt;
.....


my procedure works till the above line, i can see the xml in my table with the gt and lt chars

what i m trying to do in the next step is
Code: Select all

 RESP := RESP.EXTRACT('/soap:Envelope/soap:Body/child::node()','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');
 IF RESP.ExistsNode('//Streets_Events_GetResponse/Streets_Events_GetResult/Event/EventType') = 1 THEN
         
SOAP_RESPOND :=  XMLTYPE(SOAP_RESPOND).EXTRACT('/soap:Envelope/soap:Body/Streets_Events_GetResponse/Streets_Events_GetResult/text()'
                                    , 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/",
                                       xmlns="http://xx/"').GETCLOBVAL();
         
          Xml_Result := XMLTYPE(DBMS_XMLGEN.CONVERT(SOAP_RESPOND, DBMS_XMLGEN.ENTITY_DECODE));
         
          INSERT INTO XXHCC_BARTECSTAGING_XML VALUES ( Xml_Result, sysdate,'Street_Events_Get SOAP Response' );
          Commit;
          StResult := Xml_Result;                                                         
      ELSE
        log_error();
END IF;


I m unable to debug and i just get the below error

Code: Select all
ORA-30625: method dispatch on NULL SELF argument is disallowed


I m just trying to check for a node and if it exists then i have the records else nothing has come back, Am i right in doing this check or is there a better way to do it.

Sr
Srinivas
Senior Member
 
Posts: 185
Joined: Mon Nov 08, 2004 10:14 am
Location: UK

Re: Removing Escape Characters from Soap Response

Postby Tim... » Fri May 25, 2012 11:22 am

Hi.

You haven't said which line is failing. What I would do to check is break down all the operations into individual pieces and see what is actually failing.

Code: Select all
l_xmltype := XMLTYPE(SOAP_RESPOND);
SOAP_RESPOND :=  l_xmltype.EXTRACT('/soap:Envelope/soap:Body/Streets_Events_GetResponse/Streets_Events_GetResult/text()'
                                    , 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/",
                                       xmlns="http://xx/"').GETCLOBVAL();
SOAP_RESPOND :=  DBMS_XMLGEN.CONVERT(SOAP_RESPOND, DBMS_XMLGEN.ENTITY_DECODE);

Xml_Result := XMLTYPE(SOAP_RESPOND);


I'm not saying this should be how the finished code should look, just how you can identify the problem bit.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Removing Escape Characters from Soap Response

Postby Srinivas » Fri May 25, 2012 12:05 pm

Tim

Tried as u said,

My response for one of the records is as follows

Code: Select all
<?xml version="1.0"?>
<Streets_Events_GetResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" RecordCount="0">
  <Errors xmlns="http://www.systems.com/Streets_Events_Get.xsd">
    <Error>
      <Result>0</Result>
      <Message/>
    </Error>
  </Errors>
</Streets_Events_GetResult>


With no records being returned for a particular id and it fails at the

Code: Select all

IF RESP.ExistsNode('//Streets_Events_GetResponse/Streets_Events_GetResult/Event/EventType') = 1 THEN


What is the best way to check then....I think the If condition doesnt satisfy if no records are being returned

S
Srinivas
Senior Member
 
Posts: 185
Joined: Mon Nov 08, 2004 10:14 am
Location: UK

Re: Removing Escape Characters from Soap Response

Postby Srinivas » Fri May 25, 2012 12:29 pm

Do you I shouldnt check for anything here, If there are records it will return the full xml and if there are no records as u can see in the above xml, there is a attribute RecordCount=0, along with the returned xml.

I can pass this xml as it is to the calling program and they can decide what to do.

Do you think it would be the wise option.

S
Srinivas
Senior Member
 
Posts: 185
Joined: Mon Nov 08, 2004 10:14 am
Location: UK

Re: Removing Escape Characters from Soap Response

Postby Tim... » Fri May 25, 2012 2:26 pm

Hi.

I think your problem is caused because you are not passing the namespace to ExistsNode. If you pass that, it should be able to detect if the node is present or not.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Removing Escape Characters from Soap Response

Postby Tim... » Tue May 29, 2012 8:52 pm

Hi.

OK. The web service allows you to get away with sending two parameters, rather than a complex type, which is nice.

If you printed out the response as I kept suggesting you would see it said there was an unexpected "/" character at the end of the URL. So you were not supplying the correct URLs. You had this:

Code: Select all
  l_url         := 'http://www.webservicex.com/globalweather.asmx/';
  l_namespace   := 'xmlns="http://www.webserviceX.NET/"';

You were meant to have this:

Code: Select all
  l_url         := 'http://www.webservicex.com/globalweather.asmx';
  l_namespace   := 'xmlns="http://www.webserviceX.NET"';


Correcting this made it work. The output is encoded XML.

Code: Select all
test@db11g> select qualcosa('Reggio Calabria', 'Italy') from dual;

QUALCOSA('REGGIOCALABRIA','ITALY')
----------------------------------------------------------------------------------------------------
&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-16&quot;?&gt;
&lt;CurrentWeather&gt;
  &lt;Location&gt;Reggio Calabria, Italy (LICR) 38-04N 015-39E 21M&lt;/Location&gt;
  &lt;Time&gt;May 29, 2012 - 03:50 PM EDT / 2012.05.29 1950 UTC&lt;/Time&gt;
  &lt;Wind&gt; from the NNW (340 degrees) at 6 MPH (5 KT) (direction variable):0&lt;/Wind&gt;
  &lt;Visibility&gt; greater than 7 mile(s):0&lt;/Visibility&gt;
  &lt;SkyConditions&gt; mostly clear&lt;/SkyConditions&gt;
  &lt;Temperature&gt; 64 F (18 C)&lt;/Temperature&gt;
  &lt;DewPoint&gt; 57 F (14 C)&lt;/DewPoint&gt;
  &lt;RelativeHumidity&gt; 77%&lt;/RelativeHumidity&gt;
  &lt;Pressure&gt; 29.97 in. Hg (1015 hPa)&lt;/Pressure&gt;

QUALCOSA('REGGIOCALABRIA','ITALY')
----------------------------------------------------------------------------------------------------
  &lt;Status&gt;Success&lt;/Status&gt;
&lt;/CurrentWeather&gt;


1 row selected.

test@db11g>


I figured you would want it unencoded, so I added a convert before returning it. The finished procedure is this.

Code: Select all
CREATE OR REPLACE FUNCTION qualcosa (city  IN  VARCHAR2, country  IN  VARCHAR2)
  RETURN VARCHAR2
AS
  l_request   soap_api.t_request;
  l_response  soap_api.t_response;
  l_return    VARCHAR2(32767);
 
  l_url          VARCHAR2(32767);
  l_namespace    VARCHAR2(32767);
  l_method       VARCHAR2(32767);
  l_soap_action  VARCHAR2(32767);
  l_result_name  VARCHAR2(32767);
BEGIN
  l_url         := 'http://www.webservicex.com/globalweather.asmx';
  l_namespace   := 'xmlns="http://www.webserviceX.NET"';
  l_method      := 'GetWeather';
  l_soap_action := 'http://www.webserviceX.NET/GetWeather';
  l_result_name := 'GetWeatherResult';
 
  l_request := soap_api.new_request(p_method       => l_method,
                                    p_namespace    => l_namespace);

  soap_api.add_parameter(p_request => l_request,
                         p_name    => 'CityName',
                         p_type    => 's:string',
                         p_value   => city);
                         
  soap_api.add_parameter(p_request => l_request,
                         p_name    => 'CountryName',
                         p_type    => 's:string',
                         p_value   => country);
                         
  l_response := soap_api.invoke(p_request => l_request,
                                p_url     => l_url,
                                p_action  => l_soap_action);

  l_return := soap_api.get_return_value(p_response  => l_response,
                                        p_name      => l_result_name,
                                        p_namespace => l_namespace);

  RETURN DBMS_XMLGEN.convert(l_return, DBMS_XMLGEN.ENTITY_DECODE);
END;
/


The output is this.

Code: Select all
test@db11g> select qualcosa('Reggio Calabria', 'Italy') from dual;

QUALCOSA('REGGIOCALABRIA','ITALY')
----------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-16"?>
<CurrentWeather>
  <Location>Reggio Calabria, Italy (LICR) 38-04N 015-39E 21M</Location>
  <Time>May 29, 2012 - 03:50 PM EDT / 2012.05.29 1950 UTC</Time>
  <Wind> from the NNW (340 degrees) at 6 MPH (5 KT) (direction variable):0</Wind>
  <Visibility> greater than 7 mile(s):0</Visibility>
  <SkyConditions> mostly clear</SkyConditions>
  <Temperature> 64 F (18 C)</Temperature>
  <DewPoint> 57 F (14 C)</DewPoint>
  <RelativeHumidity> 77%</RelativeHumidity>
  <Pressure> 29.97 in. Hg (1015 hPa)</Pressure>

QUALCOSA('REGGIOCALABRIA','ITALY')
----------------------------------------------------------------------------------------------------
  <Status>Success</Status>
</CurrentWeather>


1 row selected.

test@db11g>


Now I really must do my work. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Removing Escape Characters from Soap Response

Postby Eremita » Tue May 29, 2012 9:26 pm

Hi,

but... this:
Code: Select all
CREATE OR REPLACE FUNCTION qualcosa (city  IN  VARCHAR2, country  IN  VARCHAR2)
  RETURN VARCHAR2
AS
  l_request   soap_api.t_request;
  l_response  soap_api.t_response;
  l_return    VARCHAR2(32767);
 
  l_url          VARCHAR2(32767);
  l_namespace    VARCHAR2(32767);
  l_method       VARCHAR2(32767);
  l_soap_action  VARCHAR2(32767);
  l_result_name  VARCHAR2(32767);
BEGIN
  l_url         := 'http://www.webservicex.com/globalweather.asmx';
  l_namespace   := 'xmlns="http://www.webserviceX.NET"';
  l_method      := 'GetWeather';
  l_soap_action := 'http://www.webserviceX.NET/GetWeather';
  l_result_name := 'GetWeatherResult';
 
  l_request := soap_api.new_request(p_method       => l_method,
                                    p_namespace    => l_namespace);

  soap_api.add_parameter(p_request => l_request,
                         p_name    => 'CityName',
                         p_type    => 's:string',
                         p_value   => city);
                         
  soap_api.add_parameter(p_request => l_request,
                         p_name    => 'CountryName',
                         p_type    => 's:string',
                         p_value   => country);
                         
  l_response := soap_api.invoke(p_request => l_request,
                                p_url     => l_url,
                                p_action  => l_soap_action);

  l_return := soap_api.get_return_value(p_response  => l_response,
                                        p_name      => l_result_name,
                                        p_namespace => l_namespace);

  RETURN DBMS_XMLGEN.convert(l_return, DBMS_XMLGEN.ENTITY_DECODE);
END;
/


is my function?? :)

Cheers
Eremita
Member
 
Posts: 13
Joined: Fri May 25, 2012 2:49 pm

Re: Removing Escape Characters from Soap Response

Postby Tim... » Tue May 29, 2012 9:42 pm

Hi.

Almost. I took the function you provided and,

- Altered the two URLs, as I mentioned in my previous post. The ones you used were wrong because you included an extra "/" on the end of the URLs. The fault returned by the web service said it didn't like the trailing "/", so I removed them from the URLs.

- I added the call to DBMS_XMLGEN.convert before returning the value, so the XML is displayed properly, rather than in an encoded state.

You said you wanted an example of your web service call working. That's it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Removing Escape Characters from Soap Response

Postby Eremita » Wed May 30, 2012 9:05 am

Tim... wrote:Hi.

Almost. I took the function you provided and,

- Altered the two URLs, as I mentioned in my previous post. The ones you used were wrong because you included an extra "/" on the end of the URLs. The fault returned by the web service said it didn't like the trailing "/", so I removed them from the URLs.

- I added the call to DBMS_XMLGEN.convert before returning the value, so the XML is displayed properly, rather than in an encoded state.

You said you wanted an example of your web service call working. That's it.

Cheers

Tim...


But.. Do you have edit function from package soap_api??
I added your code (DBMS_XMLGEN.convert ) and i have always error :(
Code: Select all
ORA-20000: soap:Client - System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://www.webservicex.com/GetWeather.
   at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest()
   at System.Web.Services.Protocols.SoapServerProtocol.RouteRequest(SoapServerMessage message)
   at System.Web.Services.Protocols.SoapServerProtocol.Initialize()
   at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean&amp; abortProcessing)
ORA-06512: at "SYS.SOAP_API", line 95
ORA-06512: at "SYS.SOAP_API", line 132
ORA-06512: at "SYS.QUALCOSA", line 36

And i see that complex parameters are treated as normal parameters:
Code: Select all
soap_api.add_parameter(p_request => l_request,
                         p_name    => 'CityName',
                         p_type    => 's:string',
                         p_value   => city);
                         
  soap_api.add_parameter(p_request => l_request,
                         p_name    => 'CountryName',
                         p_type    => 's:string',
                         p_value   => country);


Wrong?

Cheers
Eremita
Member
 
Posts: 13
Joined: Fri May 25, 2012 2:49 pm

Next

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: sellyh19 and 3 guests