Home  /  Application Development: Core Topics  /  InterSystems SQL Reference  /  SQL Functions  /  XMLELEMENT

InterSystems SQL Reference
[Back]  [Next] 
InterSystems: The power behind what matters   

A function that formats an XML markup tag to enclose one or more expression values.
XMLELEMENT([NAME] tag,expression[,expression])

XMLELEMENT([NAME] tag,XMLATTRIBUTES(expression [AS alias]),expression[,expression])
NAME tag
The name of an XML markup 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.
XMLELEMENT performs no validation of tag values. However, the XML standard requires that a valid tag name cannot contain any of the characters !"#$%&'()*+,/;<=>?@[\]^`{|}~, nor a space character, and cannot begin with "-", ".", or a numeric digit.
If you specify the NAME keyword without a tag value, InterSystems IRIS 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.
The XMLELEMENT function returns the values of expression tagged with the XML (or HTML) markup start-tag and end-tag specified in tag. For example, XMLELEMENT(NAME "Para",Home_City) returns values such as the following: <Para>Chicago</Para>. XMLELEMENT cannot be used to generate an empty-element 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.
The tag argument uses double quotes to enclose a literal string. In nearly all other contexts, InterSystems 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.
When SQL code is specified as a string delimited by double quotes, such as in a Dynamic SQL %Prepare() method, you must escape the tag double quotes by specifying two double quotes, as follows:
  SET myquery = "SELECT XMLELEMENT(""Para"",Name) FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
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. An expression can be a field of any type, including a data stream field. The specified expression value is returned enclosed by a start tag and an end tag, as shown in the following format:
If the value to be tagged is either the empty string ('') value or a NULL, the following is returned:
If the expression contains multiple comma-separated elements, the results are concatenated, as shown in the following format:
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).
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. InterSystems IRIS encloses attribute values with single quotes and inserts a space between the attribute value and the closing angle bracket (>) for the tag.
Punctuation Character Values
If a data value contains a punctuation character that XML/HTML might interpret as a tag or other coding, XMLELEMENT and XMLFOREST 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.
The following example returns each person’s Name field value in Sample.Person as ordinary data and as xml tagged data:
       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>. A blank space expression is specified to prevent concatenation of the city name and the state name:
   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:
   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 returns a character stream field as xml tagged data:
SELECT Name,Notes,
     FROM Sample.Employee WHERE Notes IS NOT NULL
A sample row of the data returned would appear as follows:
Emerson,Molly N. 5%Stream.GlobalCharacter^Sample.PersonS <Para>5%Stream.GlobalCharacter^Sample.PersonS</Para>
The following example shows that XMLELEMENT functions can be nested:
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 XMLELEMENT functions using a subquery value:
   (SELECT XMLELEMENT("NameSub",Name) FROM Sample.Person where id = 2)) AS ExportName
FROM Sample.Person WHERE ID=1
A sample row of the data returned would appear as follows:
<Para>Zucherro,Rob F.38405<Emphasis>1</Emphasis>71<NameSub>Quixote,Mark N.</NameSub></Para>
The following example shows that XMLELEMENT can not tag a value within a CASE statement clause:
      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:
     FROM Sample.Person
A sample row of the data returned would appear as follows:
<Para ID='101' >Emerson,Molly N.</Para>
You can specify an alias for an attribute, as shown in the following example:
     FROM Sample.Person
A sample row of the data returned would appear as follows:
<Para ItemKey='101' >Emerson,Molly N.</Para>
See Also
XMLAGG function
XMLCONCAT function
XMLFOREST function
SELECT statement