Removing Escape Characters from Soap Response

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

OakTable Member · by **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...

by **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

OakTable Member · by **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...

by **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

OakTable Member · by **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...

by **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"><?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="36"> <Event xmlns="http://www.systems.com/Streets_Events_Get.xsd"> <EventType>STREET COMPLETED</EventType> <EventDate>2011-09-22T08:01:03</EventDate> <USRN>19101705</USRN> <EventLocation> <BNG Easting="0" Northing="0" /> <Metric Latitude="53.789210" Longitude="-0.328330" /> </EventLocation> <DeviceName>1976 - R5</DeviceName> <DeviceType>Ranger</DeviceType> <SheetName>R5-THU-BLUE-220911</SheetName> <BinTypes>RECYCLABLES; </BinTypes> <RecordStamp> <AddedBy>wc2010_hull_ranger</AddedBy> <DateAdded>2011-09-22T08:03:52</DateAdded> <Comments /> </RecordStamp> </Event> <Event xmlns="http://www.systems.com/Streets_Events_Get.xsd"> <EventType>STREET COMPLETED</EventType> <EventDate>2011-04-21T07:58:12</EventDate> <USRN>19101705</USRN> <EventLocation> <BNG Easting="0" Northing="0" /> <Metric Latitude="53.789120" Longitude="-0.328300" /> </EventLocation> <DeviceName>1976 - R5</DeviceName> <DeviceType>Ranger</DeviceType> <SheetName>R5-THU-BLUE-210411</SheetName> <BinTypes>RECYCLABLES; </BinTypes> <RecordStamp> <AddedBy>wc2010_hull_ranger</AddedBy> <DateAdded>2011-04-21T07:58:51</DateAdded> <Comments /> </RecordStamp> </Event> <Event xmlns="http://www.systems.com/Streets_Events_Get.xsd"> <EventType>STREET COMPLETED</EventType> <EventDate>2011-04-14T07:41:00</EventDate> <USRN>19101705</USRN> <EventLocation> <BNG Easting="0" Northing="0" /> <Metric Latitude="53.789240" Longitude="-0.328260" /> </EventLocation> <DeviceName>1976 - R5</DeviceName> <DeviceType>Ranger</DeviceType> <SheetName>R5-THU-BROWN-140411</SheetName> <BinTypes>ORGANIC; </BinTypes> <RecordStamp> <AddedBy>wc2010_hull_ranger</AddedBy> <DateAdded>2011-04-14T07:41:59</DateAdded> <Comments /> </RecordStamp> </Event> <Event xmlns="http://www.systems.com/Streets_Events_Get.xsd"> <EventType>STREET COMPLETED</EventType> <EventDate>2011-04-07T08:10:00</EventDate> <USRN>19101705</USRN> <EventLocation> <BNG Easting="0" Northing="0" /> <Metric Latitude="53.789230" Longitude="-0.328220" /> </EventLocation> <DeviceName>1976 - R5</DeviceName> <DeviceType>Ranger</DeviceType> <SheetName>R5-THU-BLUE-070411</SheetName> <BinTypes>RECYCLABLES; </BinTypes> <RecordStamp> <AddedBy>wc2010_hull_ranger</AddedBy> <DateAdded>2011-04-07T08:11:48</DateAdded> <Comments /> </RecordStamp> .....

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

OakTable Member · by **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...

by **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

by **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

OakTable Member · by **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...

OakTable Member · by **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') ---------------------------------------------------------------------------------------------------- <?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>

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...

by **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

OakTable Member · by **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...

by **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& 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

Removing Escape Characters from Soap Response

Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Re: Removing Escape Characters from Soap Response

Who is online