Webservice response is returned as a String?

[Former Member]

(SQL Anywhere 12)

I have a web service that returns part of the response as a String inside the XML, I want this also to “be” a XML, to be able to remove [CDATA[ and /DATA

How it looks today:

    < SOAP-ENV:Body >
      < tns:GetResponse >
         < tns:GetResult xsi:type="tns:SimpleDataset" >
            < tns:rowset >
               < tns:row >
               < tns:response >

< ![CDATA[< MyData >< column1 >123< /column1 >< column2 >147< /column2 >< column3 >248< /column3 > < /MyData > < /DATA >]]

< /tns:response >
               < /tns:row >
             < /tns:rowset >
          < /tns:GetResult >
         < tns:sqlcode >0< /tns:sqlcode >
       < /tns:GetResponse >
    < /SOAP-ENV:Body >

So what I want is something like this:

   < SOAP-ENV:Body >
      < tns:GetResponse >
         < tns:GetResult xsi:type="tns:SimpleDataset" >
            < tns:rowset >
               < tns:row >
               < tns:response >

< tns:MyData >
                    < tns:column1 >123
                    < tns:column2 >147
                    < tns:column3>248
                 < /tns:MyData >

< /tns:response >
              < /tns:row >
            < /tns:rowset >
         < /tns:GetResult >
       < tns:sqlcode >0< /tns:sqlcode >
     < /tns:GetResponse >
   < /SOAP-ENV:Body >

The web service

CREATE SERVICE "MyName/GetMyData" TYPE 'SOAP' AUTHORIZATION OFF USER "DBA" AS select GetMyData(:column1,:column2,:column3) as response;

Can this be done or am I stuck with [ CDATA [ and / DATA in my SOAP XML?

I have tried different FORMAT 'XML' and 'CONCRETE' when creating the webservice, but it srill returns a string

would be very happy if someone could point me in the right direction

[Former Member · Accepted solution]

Well I got the solution now....

Just the ordinary procedure with a select without any XML functions at all or any cast

CREATE PROCEDURE GetMyData( @column1 integer, @column2 integer, @column3 integer ) 
BEGIN SELECT * FROM xtable
 WHERE column1 = @column1 and column2 = @column2 and column3 = @column3 
END

Your procedure should NOT return any XML code at all

In creating the service the format must be set to 'CONCRETE'

CREATE SERVICE "MyName/GetMyData" TYPE 'SOAP' AUTHORIZATION OFF USER "DBA" FORMAT 'CONCRETE' AS call GetMyData(:column1,:column2,:column3;

the columns in the select list in your procedure will be presented as element in the (SOAP) XML

(ofcourse your wsdl must also describe the result out from the webservice correct, forgot to write that in my previous comment)

[MarkCulp]

What is the definition of your GetMyData procedure? function?

My guess is that your procedure is return varchar instead of XML! When the result data type is not XML then the server will automatically do what is necessary to fit into the SOAP envelope and this means munging the data so that an XML parse can properly parse it.

If you are already generating XML but returning it as char/varchar type (as I suspect) then change your procedure to describe the result (or return) type to be XML. Note you will most likely also need to change your procedure to actually produce XML ... which means that you will need to generate XML directly or cast your varchar data to XML (e.g. cast( mydata as XML )).

You may also want to use the XMLELEMENT, XMLCONCAT, and related XML functions.

[Former Member]

Thank you for your answer,

I use a function to retrieve the data needed, but I can not see anything wrong with the function, To me it looks like it is returning a XML!

I did get another response when the webservice was created with

CREATE SERVICE "MyName/GetMyData" TYPE 'SOAP' AUTHORIZATION OFF USER "DBA" FORMAT 'XML' AS select GetMyData(:column1,:column2,:column3) as response;

Then < tns:rowset > and < tns:row > was also included in the string, that's why I thought I did something wrong creating the webservice

This is how the function looks like:

CREATE FUNCTION GetMyData( @column1 integer, @column2 integer, @column3 integer )
RETURNS XML
DETERMINISTIC
BEGIN
   DECLARE return_value XML; 
   SET return_value = CAST((SELECT * FROM xtable WHERE column1 = @column1 and column2 = @column2 and column3 = @column3 FOR XML AUTO, ELEMENTS) as XML);
   RETURN return_value;
END

[Former Member]

I still have not found a solution and if anyone can see what is wrong, I would be grateful if you could point it out

[VolkerBarth]

Could you give an example of the return value from your stored function?

(I'd think it looks OK and follows Mark's suggestion, so I don't have a clue why it is put inside a CDATA section.)

[Former Member]

The answer from my function is

< MyData >< column1 >123< /column1 >< column2 >147< /column2 >< column3 >248< /column3 > < /MyData >

I have experimented with using a procedure instead, but that does not help, different values on 'format' (in the creation of the web service) and using different xml functions in my procedure/function hoping that it would not result in a CDATA section but no success, I can see a difference in the result, but the result itself is always included in a CDATA section...

but the weird part is that after have made it into a procedure and just leave it like this (without any xml)

CREATE PROCEDURE GetMyData( @column1 integer, @column2 integer, @column3 integer ) BEGIN SELECT * FROM xtable WHERE column1 = @column1 and column2 = @column2 and column3 = @column3 END

CREATE SERVICE "MyName/GetMyData" TYPE 'SOAP' AUTHORIZATION OFF USER "DBA" FORMAT 'XML' AS call GetMyData(:column1,:column2,:column3;

I get the same result as I wrote before, so the cast or FOR XML does not make a difference..

Or is there any other type of setting you should also change? Is there anywhere else you should define that the answer out from the webservice should be viewed as an XML?