Caché SQL Reference
XMLFOREST
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A function that tags values returned from multiple columns.
Synopsis
XMLFOREST(expression [AS tag][,expression [AS tag]])
Arguments
expression An expression, or (usually) a comma-separated list of expressions. Usually these are names of columns that contains the data values to be tagged. Each expression in the list will be enclosed in its own tag.
AS tag Optional — The value of the tag for the preceding expression. If tag is unquoted, the first character of tag must be a letter; blanks and quotation marks are not permitted in tag. Enclosing tag in double quotes removes these restrictions. If tag is omitted, the tag value is either the name of the expression column or an empty tag.
Description
The XMLFOREST function returns the values of each expression tagged with its own XML (or HTML) tag.
XMLFOREST can be used in a SELECT query or subquery that references either a table or a view. XMLFOREST can appear in a SELECT list alongside ordinary column values.
The specified expression value is returned enclosed by a start tag and an end tag, as shown in the following format:
<tag>value</tag>
Commonly, expression is the name of a column, or an expression containing one or more column names. XMLFOREST tags each expression as follows:
XMLFOREST provides a separate tag for each item in a comma-separated list. XMLELEMENT concatenates all of the items in a comma-separated list within a single tag.
XMLFOREST functions can be nested. Any combination of nested XMLFOREST and XMLELEMENT functions is permitted. XMLFOREST functions can be concatenated using XMLCONCAT.
NULL Values
The XMLFOREST function only returns a tag for actual data values. It does not return a tag when the expression value is NULL. The empty string ('') is considered a data value. If the value to be tagged is the empty string (''), XMLFOREST returns:
<tag></tag>
XMLFOREST differs from XMLELEMENT in the handling of NULL. XMLELEMENT always returns a tag value, even when the field value is NULL.
Punctuation Character Values
If a data value contains a punctuation character that XML/HTML might interpret as a tag or other coding, XMLFOREST and XMLELEMENT convert this character to the corresponding encoded form:
ampersand (&) becomes &amp;
apostrophe (') becomes &apos;
quotation mark (") becomes &quot;
open angle bracket (<) becomes &lt;
close angle bracket (>) becomes &gt;
To represent an apostrophe in a supplied text string, specify two apostrophes, as in the following example: 'can''t'. Doubling apostrophes is not necessary for column data.
Examples
The following query returns the Name column values in Sample.Person as ordinary data and as xml tagged data:
SELECT Name,XMLFOREST(Name) AS ExportName
     FROM Sample.Person
 
A sample row of the data returned would appear as follows. Here the tag defaults to the name of the column:
Name                    ExportName
Emerson,Molly N.   <NAME>Emerson,Molly N.</NAME>
The following example specifies multiple columns:
SELECT XMLFOREST(Home_City,
                 Home_State AS Home_State,
                 AVG(Age) AS AvAge) AS ExportData
FROM Sample.Person
 
The Home_City field specifies no tag; the tag is generated from the column name in all capital letters: <HOME_CITY>. The Home_State field’s AS clause is optional. It is specified here because specifying the tag name allows you to control the case of the tag: <Home_State>, rather than <HOME_STATE>. The AVG(Age) AS clause is mandatory, because the value is an aggregate, not a column value, and thus has no column name. A sample row of the data returned would appear as follows.
ExportData
<HOME_CITY>Chicago</HOME_CITY><Home_State>IL</Home_State>
<AvAge>48.0198019801980198</AvAge>
See Also
XMLAGG function
XMLELEMENT function
XMLCONCAT function
SELECT statement