SQL XMLPARSE

What is SQL XMLPARSE?

XMLPARSE converts a character string that contains XML markup into a native XML data 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 XMLPARSE: PostgreSQL 8.2+, IBM Db2, Oracle 10g+, Firebird 3+, DuckDB, any engine that implements SQL/XML; not supported in MySQL or SQL Server

SQL XMLPARSE Full Explanation

XMLPARSE is part of the SQL/XML standard (first added in SQL:2003). It takes a VARCHAR, CHAR, CLOB, or similar character string and validates it as either a full XML document or an XML content fragment, returning a value of the XML data type. Choosing DOCUMENT enforces exactly one top-level element, while CONTENT allows multiple top-level nodes such as comments or processing instructions. Optional whitespace handling lets you keep or trim insignificant whitespace. If the supplied string is not well-formed according to the chosen mode, the statement raises an error. The result can be stored in XML columns or passed to other XML functions such as XMLQUERY or XMLTABLE. Because parsing is done at run time, performance can be affected by very large strings, so indexes on XML columns or pre-validated storage are recommended in high-volume systems.

SQL XMLPARSE Syntax

XMLPARSE ( { DOCUMENT | CONTENT } string_expression [ PRESERVE WHITESPACE | STRIP WHITESPACE ] )

SQL XMLPARSE Parameters

mode (DOCUMENT | CONTENT) - keyword that defines validation rules
string_expression (string) - the character string containing XML text
whitespace (optional) - PRESERVE WHITESPACE keeps all insignificant whitespace, STRIP WHITESPACE removes it

Example Queries Using SQL XMLPARSE

-- Parse a well-formed XML document
SELECT XMLPARSE(DOCUMENT '<invoice id="25"><total>125.00</total></invoice>') AS doc;

-- Parse a content fragment (no single root element)
SELECT XMLPARSE(CONTENT '<li>One</li><li>Two</li>') AS fragment;

-- Store parsed XML into a table column
INSERT INTO invoices_xml(id, data)
VALUES (25, XMLPARSE(DOCUMENT :invoice_clob PRESERVE WHITESPACE));

Expected Output Using SQL XMLPARSE

Each statement returns a value of the XML type
If the input is not well-formed (or violates DOCUMENT vs CONTENT rules), the database throws a parse error and the command fails

Use Cases with SQL XMLPARSE

Load XML received as text into XML columns for further XQuery processing
Validate incoming XML payloads during ETL to catch bad data early
Convert string literals to XML inside ad-hoc queries without temporary tables
Strip insignificant whitespace to save storage while keeping semantic content

Common Mistakes with SQL XMLPARSE

Supplying multiple root elements while using DOCUMENT mode
Assuming XMLPARSE exists in every database; some dialects rely on CAST or proprietary functions
Forgetting to specify PRESERVE or STRIP when whitespace handling matters
Passing non-XML text, leading to runtime parse errors

First Introduced In

SQL:2003 (SQL/XML)

Frequently Asked Questions

What is the difference between XMLPARSE and XMLSERIALIZE?

XMLPARSE converts text to the XML type, while XMLSERIALIZE converts an XML value back to text.

Can I parse invalid XML with XMLPARSE?

No. The function validates the string. If it is not well-formed according to the chosen mode, the statement fails with a parse error.

Does XMLPARSE impact performance?

Parsing large XML documents can be CPU intensive. Consider storing pre-validated XML or using indexes on XML columns to speed up downstream queries.

Why does my query fail with "not well-formed" even though the XML looks correct?

Check that you are using the correct mode (DOCUMENT vs CONTENT) and that the encoding matches the string's actual encoding. Also verify that special characters are properly escaped.