PostgreSQL 2200L Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2200L: not an xml document

PostgreSQL error 2200L occurs when a string or value passed to an XML function or cast to the xml type does not conform to the structure of a valid XML document. Unlike an XML fragment (which may have multiple root nodes), an XML document must have exactly one root element and be well-formed. This error is common when working with XMLPARSE, xpath(), xmltable(), or direct ::xml casting.

Top 3 Causes

1. Parsing an XML Fragment as a Document

PostgreSQL distinguishes between DOCUMENT and CONTENT modes. Passing multiple root nodes or a rootless string to XMLPARSE(DOCUMENT ...) will trigger this error.

-- ERROR: not an xml document (multiple root nodes)
SELECT XMLPARSE(DOCUMENT '<item>apple</item><item>banana</item>');

-- FIX 1: Use CONTENT mode for fragments
SELECT XMLPARSE(CONTENT '<item>apple</item><item>banana</item>');

-- FIX 2: Wrap in a root element
SELECT XMLPARSE(DOCUMENT '<items><item>apple</item><item>banana</item></items>');

2. Malformed XML (Not Well-Formed)

Missing closing tags, improper nesting, or unescaped special characters (&, <, >) will cause this error.

-- ERROR: missing closing tag
SELECT '<name>John Doe'::xml;

-- ERROR: unescaped ampersand
SELECT '<price>10 & 20</price>'::xml;

-- FIX: Escape special characters properly
SELECT '<price>10 &amp; 20</price>'::xml;

-- FIX: Ensure proper tag closure
SELECT '<name>John Doe</name>'::xml;

3. Unvalidated External Data

Data arriving from APIs, file imports, or legacy systems may look like XML but contain HTML tags, encoding issues, or plain text mixed in.

-- Safe parsing function to avoid transaction rollbacks
CREATE OR REPLACE FUNCTION safe_parse_xml(p_input TEXT)
RETURNS XML AS $$
BEGIN
    RETURN p_input::xml;
EXCEPTION
    WHEN SQLSTATE '2200L' THEN
        RAISE WARNING 'Invalid XML document: %', LEFT(p_input, 200);
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Usage: returns NULL instead of raising an error
SELECT safe_parse_xml('<valid><node>ok</node></valid>');   -- returns xml
SELECT safe_parse_xml('<broken>no closing tag');           -- returns NULL

Quick Fix Solutions

-- Check if a value is a valid XML document before inserting
DO $$
DECLARE
    v_input TEXT := '<root><item>test</item></root>';
    v_xml   XML;
BEGIN
    BEGIN
        v_xml := v_input::xml;
        RAISE NOTICE 'Valid XML: %', v_xml;
    EXCEPTION
        WHEN SQLSTATE '2200L' THEN
            RAISE WARNING 'Skipping invalid XML document.';
    END;
END;
$$;

-- Safe xpath usage with null guard
SELECT
    id,
    (xpath('//title/text()', data))[1]::TEXT AS title
FROM documents
WHERE data IS NOT NULL;

Prevention Tips

1. Validate XML at the application layer before sending to PostgreSQL.
Use libraries like Python's lxml, Java's DocumentBuilder, or Node's fast-xml-parser to validate XML before it reaches the database. Catching errors early avoids costly transaction rollbacks.

2. Standardize on CONTENT vs DOCUMENT mode across your codebase.
If your data can include fragments (multiple root nodes), always use XMLPARSE(CONTENT ...). Reserve XMLPARSE(DOCUMENT ...) only when a single root element is guaranteed. Document this decision in your team's coding standards to avoid inconsistency.

-- Recommended safe insert pattern
INSERT INTO xml_store (payload)
VALUES (XMLPARSE(CONTENT :user_input_text));

Related Errors

Code	Name
`2200M`	invalid XML content
`2200N`	invalid XML name
`22000`	data exception (parent category)

📖 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.