SQL XMLCONCAT

What is SQL XMLCONCAT and how is it used?

XMLCONCAT concatenates two or more XML fragments into a single XML value.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL XMLCONCAT: Supported: PostgreSQL 8.3+, Oracle Database 9i+, IBM Db2, SAP HANA, Firebird, Vertica. Not natively supported: MySQL, SQL Server, SQLite.

SQL XMLCONCAT Full Explanation

XMLCONCAT is an SQL/XML scalar function defined by the SQL:2003 standard that merges multiple XML values into one. Each argument must be of type XML. Null arguments are ignored; if every argument is NULL, the function returns NULL. XMLCONCAT performs a shallow concatenation - it simply appends the node sequences of the inputs without wrapping them in a new root element or preserving XML declarations. As a result, the output is a well-formed XML forest only if the caller ensures the combined nodes form a valid document or are later wrapped (for example, with XMLELEMENT). Because no implicit casting from text to XML occurs in most databases, string literals usually need an explicit cast to XML. The function runs in the SQL engine, making it faster and more secure than concatenating XML in client code.

SQL XMLCONCAT Syntax

XMLCONCAT(xml_value1 [, xml_value2 ...]);

SQL XMLCONCAT Parameters

- xml_valueN (XML) - One or more XML expressions to be concatenated. Unlimited positional parameters are allowed.
- Returns (XML) - A single XML value representing the concatenated input.

Example Queries Using SQL XMLCONCAT

-- Concatenate two literal XML fragments (PostgreSQL)
SELECT XMLCONCAT('<a>foo</a>'::xml, '<b>bar</b>'::xml) AS result;

-- Build a full-name XML fragment from table columns
SELECT XMLCONCAT(
         XMLELEMENT(NAME first_name, first_name),
         XMLELEMENT(NAME last_name,  last_name)
       ) AS full_name_xml
FROM   employees
WHERE  employee_id = 101;

-- Wrap the concatenation to guarantee a single root element
SELECT XMLELEMENT(NAME employee,
         XMLCONCAT(address_xml, contact_xml)) AS employee_document
FROM   hr.employee_data;

Expected Output Using SQL XMLCONCAT

Each query returns a single XML value that contains the sequential nodes of all non-NULL inputs
No additional root element or whitespace is inserted
If all inputs are NULL, the result is NULL

Use Cases with SQL XMLCONCAT

Combine several XML fragments produced by XMLELEMENT/XMLFOREST into one payload before serialization.
Assemble variable XML sections (header, body, footer) into a single message.
Merge optional XML columns when NULL handling is required.
Prepare XML responses inside stored procedures without client-side string manipulation.

Common Mistakes with SQL XMLCONCAT

Forgetting to cast string literals to XML, causing a type error.
Assuming XMLCONCAT creates a new root element. It does not.
Ignoring NULLs and expecting them to appear as empty tags.
Producing an invalid XML document by concatenating fragments that together lack a single root.

First Introduced In

SQL:2003 (SQL/XML)

Frequently Asked Questions

Does XMLCONCAT add a root element?

No. It simply appends the node sequences of its inputs. Use XMLELEMENT to wrap the result if you need a single root.

What happens when one argument is NULL?

NULL arguments are skipped. If all arguments are NULL, XMLCONCAT returns NULL.

How is XMLCONCAT different from string concatenation?

XMLCONCAT works on typed XML values, preserving node structure and ensuring the output remains valid XML. String functions do not validate XML.

How can I ensure the output is a well-formed document?

Wrap the XMLCONCAT call inside an XMLELEMENT (or similar) to provide a single root, or guarantee that the concatenated fragments already share one.