XML Web Service

[Former Member]

ASA 9.0.2 I have a Web Service that exposes an XML result set, my problem is that I want complete control over the XML generated, and Sybase keeps wrapping a <root> element around my result. I already have a root element by another name (other than 'root'). How can I stop the addition of this 'root' node?

Example of my result set: <Settings> <SettingOne>SettingValue</SettingOne> </Settings>

Sybase is adding <root></root> tags around that result, I need to remove/suppress/prevent this ..

[MarkCulp]

Please show us exactly what you are doing.

[Former Member]

Ok, so I have a Service that's being created as follows:

CREATE SERVICE "MyGroup/GetSettings" TYPE 'XML' AUTHORIZATION ON USER DBA AS CALL sp_GetSettings()

backing stored procedure that returns an XML result set like:

CREATE PROCEDURE sp_GetSettings AS
SELECT XMLELEMENT(NAME "Settings", XMLELEMENT(NAME "SettingOne", 1)) FROM DUMMY

When I hit the web service, the XML result returned adds a node called 'root' around my result; that's what I'm trying to overcome; I want the parent node, the root node, to be 'Settings'

[MarkCulp · Accepted solution]

The design of the web service type 'XML' is to:

• convert the result set to XML if needed - since your result set is already XML this step is not needed, and
• always wrap the result set from the procedure in an outer "root" node.

There is currently no plans to change this behaviour.

You have two options:

• Leave your web service as is and remove the "root" node from the XML in your client
• Switch your web service from type XML to type RAW

Type 'RAW' web services will return to the client exactly the output from the SQL procedure with no additional text added. E.g.

CREATE SERVICE "MyGroup/GetSettings" 
  TYPE 'RAW' AUTHORIZATION ON USER DBA
  AS CALL sp_GetSettings(;

CREATE PROCEDURE sp_GetSettings()
RESULT ( rawdoc long varchar )
BEGIN
  SELECT XMLELEMENT(NAME "Settings", XMLELEMENT(NAME "SettingOne", 1)) FROM DUMMY;
END;

Note that the sp_GetSettings procedure declares its result set as a long varchar. Since the output is a character type the web service call will automatically do character set conversion between the database character set and the client character set as needed. If you don't want this character set conversion to occur you need to either:

• declare the result type as long binary, or
• call sa_set_http_option( 'CharsetConversion', 'OFF' );

HTH

[Former Member · Accepted solution]

Mark,

Thank you for the answer, I appreciate the help.

[Dan_Cummins]

Hi Doug,

Are you looking for the '@mp:xmltext' property to get a subtree of the XML document in XML form?

SELECT * FROM openxml( '<root><Settings> <SettingOne>SettingValue</SettingOne> </Settings></root>', '//*' )
 WITH (ID                   int '@mp:id',
       parent               int '../@mp:id',
        name                char(128) '@mp:localname',
        othertext           XML '@mp:xmltext'
       )
ORDER BY ID;

Produces these results in DBISQL's results window (cut-and-pasted):

ID,parent,name,othertext
5,,'root',<root><Settings> <SettingOne>SettingValue</SettingOne> </Settings></root>
15,5,'Settings',<Settings> <SettingOne>SettingValue</SettingOne> </Settings>
28,15,'SettingOne',<SettingOne>SettingValue</SettingOne>

[Former Member]

Dan, Thanks for responding. The short answer to your question is "no", I have created a Web Services in the DB, my service is backed by a Stored Procedure that creates an XML result; so my Service type is XML. The problem I have is that the Sybase Web Service is adding a node called 'root' around my result set.

I want to prevent Sybase from adding the 'root' node to the XML result.