Caché SQL Reference
XMLFOREST
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A function that formats multiple XML markup tags to enclose expression values.
Synopsis
XMLFOREST(expression [AS tag][,expression [AS tag]])
Arguments
expression Any valid expression. Usually the name of a column that contains the data values to be tagged. When specified as a comma-separated list, each expression in the list will be enclosed in its own XML markup tag.
AS tag
Optional — The name of an XML markup tag. The AS keyword is mandatory if tag is specified. The case of letters in tag is preserved.
Enclosing tag with double quotes is optional. If you omit the double quotes, tag must follow XML naming standards. Enclosing tag with double quotes removes these naming restrictions.
XMLFOREST enforces XML naming standards for a valid tag name. It cannot contain any of the characters !"#$%&'()*+,/;<=>?@[\]^`{|}~, nor a space character, and cannot begin with "-", ".", or a numeric digit.
If you specify an expression without the AS tag clause, the tag value is the name of the expression column (in capital letters): <HOME_CITY>Chicago</HOME_CITY>.
Description
The XMLFOREST function returns the values of each expression tagged with its own XML markup start-tag and end-tag, as specified in tag. For example, XMLFOREST(Home_City AS City,Home_State AS State) returns values such as the following: <City>Chicago</City><State>IL</State>. XMLFOREST cannot be used to generate an empty-element 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. An expression can be a field of any type, including a data stream field. 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