SQL XMLROOT

What is SQL XMLROOT?

SQL XMLROOT adds or replaces the XML declaration (version and standalone attributes) at the top of an 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 XMLROOT: PostgreSQL (8.4+), Oracle (10g+), IBM Db2, Teradata. Not supported natively in MySQL, SQL Server, or SQLite.

SQL XMLROOT Full Explanation

XMLROOT is an XML-constructor function that returns a new XML value identical to the supplied XML except for its XML declaration. It is primarily used when you need the XML prolog to explicitly include or omit the version number and the standalone flag. Internally, the function does not modify element content; it only prepends or alters the declaration ''. If VERSION is supplied, the existing declaration is replaced or inserted. The SPECIAL token NO VERSION (PostgreSQL) preserves any existing version or omits the declaration entirely. The STANDALONE clause can be set to YES, NO, or omitted (meaning unchanged/absent). XMLROOT is deterministic and operates on an XML or XMLTYPE value, returning the same type. Unlike XMLSERIALIZE, it does not convert to text; it keeps the datatype. Be careful: XMLROOT will throw an error if the source is NULL, and some databases (like PostgreSQL) only allow the function inside queries, not in DDL.

SQL XMLROOT Syntax

-- PostgreSQL
XMLROOT ( xml_value
         [ , VERSION text | NO VERSION ]
         [ , STANDALONE YES | NO ] )

SQL XMLROOT Parameters

xml_value (XML) - The input XML document or fragment on which to operate.
VERSION (text) - Quoted string specifying the version attribute (e.g., '1.0').
NO VERSION (keyword) - Keeps existing declaration or omits it if none.
STANDALONE (YES/NO) - Sets the standalone attribute; omit to leave unchanged.

Example Queries Using SQL XMLROOT

-- 1. Add a version and standalone declaration
SELECT XMLROOT(XMLPARSE(DOCUMENT '<book><title>SQL</title></book>'),
               VERSION '1.0',
               STANDALONE YES) AS xml_with_decl;

-- 2. Preserve existing declaration but mark standalone as NO
SELECT XMLROOT(XMLPARSE(DOCUMENT '<?xml version="1.1"?><x></x>'),
               NO VERSION,
               STANDALONE NO);

-- 3. Oracle syntax example
SELECT XMLRoot(XMLType('<note><body>Hello</body></note>'),
               VERSION '1.0',
               STANDALONE YES) AS result
FROM dual;

Expected Output Using SQL XMLROOT

Returns: \n
Returns: \n
In Oracle, returns an XMLTYPE with the specified declaration.

Use Cases with SQL XMLROOT

Generating XML files that must conform to an external specification requiring a specific version or standalone status.
Replacing an incorrect or missing XML declaration before exporting XML from the database.
Normalizing XML output across different application components so they share the same prolog.

Common Mistakes with SQL XMLROOT

Forgetting to quote the VERSION string (must be '1.0', not 1.0).
Expecting XMLROOT to serialize to text; it stays in XML datatype.
Using VERSION and NO VERSION together (syntax error).
Applying XMLROOT to non-XML data types, which raises a type mismatch error.

First Introduced In

ISO SQL:2006 XML feature; first implemented in PostgreSQL 8.4

Frequently Asked Questions

What arguments are required for XMLROOT?

Only the input XML value is mandatory. VERSION and STANDALONE are optional modifiers.

Can I remove an existing XML declaration with XMLROOT?

Yes. In PostgreSQL, use the NO VERSION keyword without specifying STANDALONE to strip the declaration.

Does XMLROOT output a string or XML?

It returns the native XML/XMLTYPE data type, not plain text. Use XMLSERIALIZE if you need a string.

Is XMLROOT ANSI standard SQL?

XMLROOT is part of the ISO SQL XML feature set introduced in SQL:2006, but support varies by database.