SQL Anywhere 17: convert rowtype to XML

[fvestjens]

Is there a way to convert a row type in XML format?

create or replace variable obj_User row(Id integer,Firstname varchar(16),Lastname varchar(64));
select row('1','Frank', 'Vestjens') into obj_User;
select Obj_User "Users" for json raw

Is working fine. However I would like to have something similar for XML. But select obj_User "Users" for xml raw does not work.

Any ideas?

[VolkerBarth]

I don't think there is a generic way to do so, as a row data type is simply not allowed as return value of an outer select statement.

select obj_User

returns the expected SQLCODE -1599 "Invalid use of collection type". (IMHO, it's somewhat surprising that FOR JSON does work here...)

So I guess you would need to decompose the row data type in its individual members, such as

select obj_User.Id, obj_User.Firstname, obj_User.Lastname for xml auto, elements
-- returns 
<dummy><id>1</id><firstname>Frank</firstname><lastname>Vestjens</lastname></dummy>

You might need the cumbersome FOR XML EXPLICIT syntax to achieve a particular XML format.

[VolkerBarth]

(That being said, is there a particular need to use a row data type variable here? I'm asking as using SELECT ...FROM SomeTable...FOR XML is quite straight forward...)

[fvestjens]

The reason I use row objects is for json generation in the database. the row data together with array(0 can define a proper layout of the json being generated. By defining that in a domain even makes it better to read.