XMLFOREST (SQL)

A function that formats multiple XML markup tags to enclose expression values.

Synopsis

XMLFOREST(expression [AS tag][,expression [AS tag]])

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:

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 expression is not a column name (for example, an aggregate function, a literal, or a concatenation of two columns) the AS tag clause is required.
If expression is a stream field, the stream value is escaped within the resulting XML value using <![CDATA[...]]>:
```
<tag><![CDATA[value]]></tag>
```

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. For example:

INSERT INTO Sample.Xmltest (f1,f2,f3) values (NULL,'Row 1',NULL)

SELECT XMLFOREST(f1,f2,f3) from Sample.Xmltest

returns: <F2>Row 1</F2>.

The empty string ('') is considered a data value for a string data type field. If the f3 value to be tagged is the empty string (''), XMLFOREST returns:

<F3></F3>

XMLFOREST differs from XMLELEMENT in the handling of NULL. XMLELEMENT always returns a tag value, even when the field value is NULL. XMLELEMENT therefore does not distinguish between a NULL or an empty string. Both are represented as <tag></tag>.

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 &

apostrophe (') becomes '

quotation mark (") becomes "

open angle bracket (<) becomes <

close angle bracket (>) becomes >

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.

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

An optional argument that specifies 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>.

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>

The following example returns character stream data:

SELECT XMLFOREST(name AS Para,Notes AS Para) AS XMLJobHistory
     FROM Sample.Employee

A sample row of the data returned would appear as follows:

XMLJobHistory
<Para>Emerson,Molly N.</Para><Para><![CDATA[Molly worked at DynaMatix Holdings Inc. as a Marketing Manager]]></Para>

The following example shows XMLFOREST functions using a subquery value:

SELECT XMLFOREST(Name,DOB,Age,
   (SELECT XMLFOREST(Name,DOB) FROM Sample.Person WHERE %ID=2) AS ExportName)
FROM Sample.Person where %ID=1