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

A function that tags values returned from a specified column or columns.
Synopsis
XMLELEMENT([NAME] tag,expression[,expression])

XMLELEMENT([NAME] tag,XMLATTRIBUTES(expression [AS alias]),expression[,expression])
Arguments
NAME tag The name of an XML (or HTML) tag. The NAME keyword is optional. This argument has three syntactical forms: NAME "tag", "tag", and NAME. The first two are functionally identical. If specified, tag must be enclosed in double quotes. The case of letters in tag is preserved. A tag should contain at least one nonnumeric character; that is, characters other than 0–9, decimal point, leading + or – sign. No other validation checking is performed on tag. If you specify the NAME keyword without a tag value, Caché supplies the default tag value: <Name> ... </Name>.
expression Any valid expression. Usually the name of a column that contains the data values to be tagged. You can specify a comma-separated list of columns or other expressions, all of which will be enclosed within the same tag. The first comma-separated element can be an XMLATTRIBUTES function. Only one XMLATTRIBUTES element can be specified.
Description
The XMLELEMENT function returns the values of expression tagged with the XML (or HTML) tag specified in tag. XMLELEMENT can be used in a SELECT query or subquery that references either a table or a view. XMLELEMENT can appear in a SELECT list alongside ordinary field values.
Note:
The tag argument uses double quotes to enclose a literal string. In nearly all other contexts, Caché SQL uses single quotes to enclose a literal string; it uses double quotes to specify a delimited identifier. Therefore, delimited identifier support must be enabled to use this feature.
Commonly, expression is the name of a field, (or an expression containing one or more field names) in the multiple rows returned by a query. The specified expression value is returned enclosed by a start tag and an end tag, as shown in the following format:
<tag>value</tag>
If the value to be tagged is either the empty string ('') value or a NULL, the following is returned:
<tag></tag>
If the expression contains multiple comma-separated elements, the results are concatenated, as shown in the following format:
<tag>value1value2</tag>
XMLELEMENT functions can be nested. XMLELEMENT and XMLFOREST functions may be nested in any combination. XMLELEMENT functions can be concatenated using XMLCONCAT. However, XMLELEMENT does not do XML type resolution of entire expressions. For example, XMLELEMENT cannot perform character conversion within a clause of a CASE statement (see example below).
XMLATTRIBUTES Function
The XMLATTRIBUTES function can only be used within an XMLELEMENT function. If an element of expression is an XMLATTRIBUTES function, the specified expression becomes an attribute of the tag, as shown in the following format:
<tag ID='63' >value</tag>
You can only specify one XMLATTRIBUTES function within an XMLELEMENT function. By convention it is the first expression element, though it can be any element in expression. Caché encloses attribute values with single quotes and inserts a space between the attribute value and the closing angle bracket (>) for the tag.
XMLELEMENT and XMLFOREST Compared
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 example returns each person’s Name field value in Sample.Person as ordinary data and as xml tagged data:
SELECT Name,
       XMLELEMENT("Para",Name) AS ExportName
     FROM Sample.Person
 
A sample row of the data returned would appear as follows:
Name                    ExportName
Emerson,Molly N.   <Para>Emerson,Molly N.</Para>
The following example returns every distinct Home_City and Home_State pair value in Sample.Person as xml tagged data with the tag <Address> ... </Address>:
SELECT DISTINCT 
   XMLELEMENT(NAME "Address",Home_City,' ',Home_State) AS CityState
   FROM Sample.Person
   ORDER BY Home_City
Note that in the above example the optional NAME keyword is supplied. In the next example, the NAME keyword is provided without the tag value:
SELECT DISTINCT 
   XMLELEMENT(NAME,Home_City,' ',Home_State) AS CityState
   FROM Sample.Person
   ORDER BY Home_City
 
In this case the same data is returned, but is tagged with the default tag: <Name> ... </Name>.
The following example shows that XMLELEMENT functions can be nested:
SELECT XMLELEMENT("Para",Home_State,
                 XMLELEMENT("Emphasis",Name),Age)
FROM Sample.Person
 
A sample row of the data returned would appear as follows:
<Para>CA<Emphasis>Emerson,Molly N.</Emphasis>24</Para>
The following example shows that XMLELEMENT can not tag a value within a CASE statement clause:
SELECT XMLELEMENT("Para",Home_State,
    XMLELEMENT("Para",Name),
    CASE WHEN Age < 21 THEN NULL
      ELSE XMLELEMENT("Para",Age) END )
FROM Sample.Person
 
A sample row of the data returned would appear as follows:
<Para>CA<Para>Emerson,Molly N.</Para>&lt;Para&gt;24&lt;/Para&gt;</Para>
The following query returns the Name field values in Sample.Person as XML-tagged data in a tag that uses the ID field as a tag attribute:
SELECT XMLELEMENT("Para",XMLATTRIBUTES(ID),Name)AS ExportName
     FROM Sample.Person
 
A sample row of the data returned would appear as follows:
ExportName
<Para ID='101' >Emerson,Molly N.</Para>
You can specify an alias for an attribute, as shown in the following example:
SELECT XMLELEMENT("Para",XMLATTRIBUTES(ID AS ItemKey),Name)AS ExportName
     FROM Sample.Person
 
A sample row of the data returned would appear as follows:
ExportName
<Para ItemKey='101' >Emerson,Molly N.</Para>
See Also
XMLAGG function
XMLCONCAT function
XMLFOREST function
SELECT statement