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

asked 06 Dec '11, 12:02

Doug%20Troy's gravatar image

Doug Troy
6112
accept rate: 0%

Please show us exactly what you are doing.

(06 Dec '11, 12:13) Mark Culp

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'

(06 Dec '11, 16:10) Doug Troy

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

permanent link

answered 06 Dec '11, 16:25

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

edited 06 Dec '11, 16:31

Mark,

Thank you for the answer, I appreciate the help.

(06 Dec '11, 16:47) Doug Troy

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>
permanent link

answered 06 Dec '11, 14:05

Dan%20Cummins's gravatar image

Dan Cummins
501613
accept rate: 41%

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.

(06 Dec '11, 15:56) Doug Troy
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×19

question asked: 06 Dec '11, 12:02

question was seen: 1,504 times

last updated: 06 Dec '11, 16:47