Oracle XMLCONCAT Function

Sometimes the XML you need is not a single element but a sequence: a processing instruction, a comment, and an element, or several sibling elements built separately. XMLCONCAT joins XML values into one fragment. With XMLCOMMENT, XMLPI, and XMLCDATA, it also covers the parts of a document that are not elements.

Code for This Guide

The main example is in the examples/xml folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

xmlconcat(xml_expr [, xml_expr ...])
xmlcomment('text')
xmlpi(name "target" [, 'value'])
xmlcdata('text')

Comments, Processing Instructions, and CDATA

This query concatenates a stylesheet processing instruction, a comment, and an element whose content is a CDATA section.

Example:

select xmlserialize(content
         xmlconcat(xmlpi(name "xml-stylesheet", 'type="text/xsl" href="fleet.xsl"'),
                   xmlcomment('Generated by Nimbus Air'),
                   xmlelement("note", xmlcdata('Fares < $500 & taxes')))
         indent) as fragments
from   dual;

Output:

FRAGMENTS
______________________________________________________
<?xml-stylesheet type="text/xsl" href="fleet.xsl"?>
<!--Generated by Nimbus Air-->
<note><![CDATA[Fares < $500 & taxes]]></note>

The text Fares < $500 & taxes appears as written inside the CDATA section; in an ordinary element, the < and & would have been escaped.

Optional Parts

XMLCONCAT skips NULL arguments, so a CASE expression that returns NULL makes a part optional. Here only the hub airport gets a hub element.

Example:

-- XMLCONCAT skips NULL arguments
select xmlserialize(content
         xmlconcat(xmlelement("code", airport_code),
                   case when is_hub then xmlelement("hub") end,
                   xmlelement("city", city))) as fragment
from   airports
where  airport_code in ('DXB', 'LHR')
order  by airport_code;

Output:

FRAGMENT
________________________________________________
<code>DXB</code><hub></hub><city>Dubai</city>
<code>LHR</code><city>London</city>

Things to Know

• The result of XMLCONCAT is a fragment, not a document, unless it has exactly one root element; wrap it in XMLELEMENT to make a document.
• XMLCONCAT works across columns of one row; XMLAGG works across rows of a group.
• A comment cannot contain two hyphens in a row, and XMLCOMMENT raises an error if it does.

Related Guides

• Oracle XMLELEMENT Function
• Oracle XMLAGG Function

Conclusion

XMLCONCAT joins XML values into one fragment and skips NULLs, which makes parts optional. XMLCOMMENT, XMLPI, and XMLCDATA create comments, processing instructions, and CDATA sections to put into it.