XMLFOREST produces a forest of XML elements from the given list of arguments. The arguments may be string expressions with optional aliases. If string expression is a column name, then you can omit the AS clause, and Virtuoso uses the partially escaped form of the column name as the name of the enclosing tag. If the expression evaluates to NULL, then no element is created for that expression. If none of the element is created, then the function returns NULL.
SQLState | Error Code | Error Text | Description |
---|---|---|---|
37000 | SQ074 | The special SQL function has invalid argument | An argument is neither a column name nor an expression with an alias. |
The following example produces five (or four) elements ('FName', 'LName', 'str', 'Title', and 'Region' - if there is a value) from the string expressions 'FirstName', 'LastName', 'string', 'Title', and 'Region', concatenates the elements produced for each employee, and produces one row for each employee in the result set.
select XMLFOREST ( "FirstName" as "FName", "LastName" as "LName", 'string' as "str", "Title", "Region" ) from "Demo"."demo"."Employees"; callret VARCHAR _______________________________________________________________________________ <FName>Nancy</FName> <LName>Davolio</LName> <str>string</str> <Title>Sales Representative</Title> <Region>WA</Region> . . . <FName>Anne</FName> <LName>Dodsworth</LName> <str>string</str> <Title>Sales Representative</Title> 9 Rows.
The following example always produces five elements: empty 'Region' element is created for NULL values. Note the difference in the last rows of this and the previous query results:
select XMLFOREST ( "FirstName" as "FName", "LastName" as "LName", 'string' as "str", "Title", coalesce ("Region", '') as "Region") from "Demo"."demo"."Employees"; callret VARCHAR _______________________________________________________________________________ <FName>Nancy</FName> <LName>Davolio</LName> <str>string</str> <Title>Sales Representative</Title> <Region>WA</Region> . . . <FName>Anne</FName> <LName>Dodsworth</LName> <str>string</str> <Title>Sales Representative</Title> <Region></Region> 9 Rows.