xml forest

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:

•  If AS tag is specified, XMLFOREST tags the resulting values with the specified tag. The tag value is case-sensitive.
•  If AS tag is omitted, and expression is a column name, XMLFOREST tags the resulting values with the column name. Column name default tags are always uppercase.
•  If AS tag is omitted, and expression is not a column name (for example, an aggregate function, a literal, or a concatenation of two columns)XMLFOREST tags the resulting values with a blank tag. For example: <>literal string</>

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>