PostgreSQL 2200L Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2200L: not an xml document

PostgreSQL error code 2200L (not an xml document) is raised when you attempt to parse or store a string as an XML document, but the input fails to meet the structural requirements of a well-formed XML document. This commonly occurs when using XML functions such as XMLPARSE(), xpath(), or when inserting data into an XML-typed column. Unlike simple syntax errors, this error can also surface when the input is technically valid XML content but lacks a single root element — a strict requirement for XML documents.

Top 3 Causes

1. Missing or Multiple Root Elements

An XML document must have exactly one root element. Passing a fragment with multiple top-level elements to XMLPARSE(DOCUMENT ...) will always trigger this error.

-- Error: two root elements
SELECT XMLPARSE(DOCUMENT '<name>John</name><age>30</age>');
-- ERROR: invalid XML document

-- Fix 1: Wrap in a single root element
SELECT XMLPARSE(DOCUMENT '<person><name>John</name><age>30</age></person>');

-- Fix 2: Use CONTENT mode for fragments
SELECT XMLPARSE(CONTENT '<name>John</name><age>30</age>');

2. Unescaped Special Characters

Characters like &, <, and > must be properly escaped in XML. Raw strings from user input or external APIs frequently contain these characters unescaped, causing the XML parser to fail immediately.

-- Error: unescaped ampersand
-- SELECT XMLPARSE(DOCUMENT '<company>AT&T</company>');

-- Fix: escape special characters before parsing
SELECT XMLPARSE(DOCUMENT 
    '<company>' || 
    REPLACE(REPLACE(REPLACE(company_name, '&', '&amp;'), '<', '&lt;'), '>', '&gt;') ||
    '</company>'
)
FROM companies
WHERE id = 1;

-- Better: use PostgreSQL's built-in XML functions to avoid manual escaping
SELECT XMLELEMENT(NAME "company", company_name)
FROM companies
WHERE id = 1;

3. Misusing XMLPARSE DOCUMENT vs CONTENT Mode

Many developers are unaware that XMLPARSE has two distinct modes. DOCUMENT mode strictly requires a fully valid XML document with a single root, while CONTENT mode accepts XML fragments. Using the wrong mode for the data at hand is a very common mistake.

-- DOCUMENT mode: requires a single root element
SELECT XMLPARSE(DOCUMENT 
    '<?xml version="1.0" encoding="UTF-8"?><catalog><item>Book</item></catalog>'
);

-- CONTENT mode: accepts fragments safely
SELECT XMLPARSE(CONTENT '<item>First</item><item>Second</item>');

-- Reusable validation helper function
CREATE OR REPLACE FUNCTION is_valid_xml_document(p_text TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM XMLPARSE(DOCUMENT p_text);
    RETURN TRUE;
EXCEPTION
    WHEN sqlstate '2200L' THEN RETURN FALSE;
    WHEN others THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Use it to filter bad rows before processing
SELECT id, raw_xml
FROM incoming_data
WHERE is_valid_xml_document(raw_xml) = FALSE;

Quick Fix Solutions

-- Validate before inserting using a CHECK constraint
ALTER TABLE xml_documents
ADD CONSTRAINT chk_valid_xml
CHECK (is_valid_xml_document(xml_data::TEXT));

-- Extract data safely with xpath (requires valid XML document)
SELECT
    id,
    (xpath('//title/text()', xml_data))[1]::TEXT AS title
FROM xml_documents;

-- Generate safe XML using built-in functions instead of string concat
SELECT XMLELEMENT(
    NAME "users",
    XMLAGG(
        XMLELEMENT(NAME "user",
            XMLFOREST(id AS "id", username AS "name"))
    )
)
FROM users;

Prevention Tips

Always use PostgreSQL's XML builder functions (XMLELEMENT, XMLFOREST, XMLAGG) instead of manually concatenating strings into XML. These functions handle escaping automatically and guarantee structural correctness.
Validate XML at the database boundary by adding a CHECK constraint or a BEFORE INSERT trigger using a helper function like is_valid_xml_document(). This catches bad data before it ever reaches your XML columns, making debugging far easier than tracking down malformed XML after the fact.

Related Errors

Error Code	Name	Description
`2200M`	invalid XML content	XML content is structurally broken
`2200N`	invalid XML comment	Malformed XML comment syntax
`2200S`	invalid XML processing instruction	Bad processing instruction format
`22000`	data exception	Parent category of 2200L

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community