Skip to main content

XMLELEMENT (SQL)

A function that formats an XML markup tag to enclose one or more expression values.

Synopsis

XMLELEMENT([NAME] tag,expression[,expression])

XMLELEMENT([NAME] tag,XMLATTRIBUTES(expression [AS alias]),expression, ...)

Description

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; delimited identifiers are enabled by default.

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. 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>

If the expression is a data stream field, the stream value is escaped within the resulting XML value using <![CDATA[...]]>:

<tag><![CDATA[value]]></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. InterSystems IRIS 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

  • XMLELEMENT concatenates the values of its expression list within a single tag. XMLFOREST assigns a separate tag for each expression item.

  • XMLELEMENT requires that you specify a tag value. XMLFOREST allows you to either take default tag values or specify individual tag values.

  • XMLELEMENT allows you to specify a tag attribute using XMLATTRIBUTES. XMLFOREST does not allow you to specify a tag attribute.

  • XMLELEMENT returns a tag string for NULL. XMLFOREST does not return a tag string for NULL.

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.

Arguments

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>. The NAME keyword is not case-sensitive; the resulting tag is initial capitalized.

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.

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>. A blank space expression is specified to prevent concatenation of the city name and the state name:

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 returns character stream data:

SELECT XMLELEMENT("Para",Name) AS XMLNotes,XMLELEMENT("Para",Notes) AS XMLText
     FROM Sample.Employee

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

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

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 XMLELEMENT functions using a subquery value:

SELECT XMLELEMENT("Para",Name,DOB, XMLELEMENT("Emphasis",%ID),Age,
   (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:

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)
     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

FeedbackOpens in a new tab