|
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? |
|
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. (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...)
(13 Feb '23, 03:17)
Volker Barth
Replies hidden
1
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.
(14 Feb '23, 09:45)
Frank Vestjens
|
