Name
XMLFOREST, xmlforest — Produces a forest of XML elements
Synopsis
XMLFOREST
(
|
string_expr1 [AS alias1] varchar, |
| string_expr2 [AS alias2] varchar , | |
| ... , | |
string_exprN [AS aliasN] varchar
); |
Description
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.
Parameters
string_exprI [AS aliasI]
string value; AS clause is mandatory if the argument is not a column name but an expression of some other sort.
Errors
Table 24.122. Errors signalled by XMLFOREST
| 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. |
Examples
Example 24.543. XMLFOREST() with five parameters.
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.