PostgreSQL 2200M Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2200M: invalid xml document

PostgreSQL error code 2200M (invalid_xml_document) is raised when a string passed to an XML-processing function or cast does not conform to the W3C XML 1.0 specification for a well-formed XML document. This typically surfaces when using XMLPARSE(DOCUMENT ...), casting text to the xml type, or calling XML functions like xpath(). PostgreSQL's XML parser is strict — even a single malformed tag or unescaped special character will trigger this error.

Top 3 Causes and Fixes

1. Missing or Mismatched Tags / No Root Element

An XML document must have exactly one root element, and every opening tag must have a corresponding closing tag in the correct order.

-- ERROR: multiple root elements (no single root)
SELECT XMLPARSE(DOCUMENT '<name>John</name><age>30</age>');
-- ERROR:  invalid xml document
-- DETAIL:  line 1: Extra content at the end of the document

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

-- ERROR: unclosed tag
SELECT XMLPARSE(DOCUMENT '<person><name>John</person>');

-- FIX: Properly close all tags
SELECT XMLPARSE(DOCUMENT '<person><name>John</name></person>');

-- TIP: Use CONTENT mode if you don't need a single root
SELECT XMLPARSE(CONTENT '<name>John</name><age>30</age>');  -- OK

2. Unescaped Special Characters

XML reserves &, <, >, ", and ' as special characters. Using them literally inside element content will break the parser.

-- ERROR: unescaped ampersand
SELECT XMLPARSE(DOCUMENT '<company>Smith & Jones LLC</company>');
-- ERROR:  invalid xml document

-- FIX 1: Use xmlelement() — it auto-escapes special characters
SELECT xmlelement(name company, 'Smith & Jones LLC');
-- Result: <company>Smith &amp; Jones LLC</company>

-- FIX 2: Manually escape before parsing
SELECT XMLPARSE(DOCUMENT
    '<company>' ||
    replace(replace('Smith & Jones LLC', '&', '&amp;'), '<', '&lt;') ||
    '</company>'
);

-- FIX 3: Use PostgreSQL built-in XML construction functions
SELECT xmlelement(
    name employee,
    xmlelement(name name, emp_name),
    xmlelement(name salary, salary)
)
FROM employees
WHERE dept = 'Engineering';

3. Encoding Declaration Mismatch or Undeclared Namespaces

If the XML prolog declares an encoding that doesn't match the actual data, or if namespace prefixes are used without being declared, the parser will reject the document.

-- ERROR: encoding mismatch in prolog
SELECT XMLPARSE(DOCUMENT '<?xml version="1.0" encoding="ISO-8859-1"?><name>Ünlü</name>');

-- FIX: Use UTF-8 (PostgreSQL's default) or omit the declaration
SELECT XMLPARSE(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?><name>Ünlü</name>');
SELECT XMLPARSE(DOCUMENT '<name>Ünlü</name>');  -- also fine

-- ERROR: undeclared namespace prefix
SELECT XMLPARSE(DOCUMENT '<ns:person><ns:name>John</ns:name></ns:person>');
-- ERROR:  invalid xml document

-- FIX: Declare the namespace
SELECT XMLPARSE(DOCUMENT '
<ns:person xmlns:ns="http://example.com/schema">
    <ns:name>John</ns:name>
</ns:person>
');

Quick Fix Toolkit

Create a reusable validation function to catch bad XML before it hits your tables:

-- Validation helper function
CREATE OR REPLACE FUNCTION is_valid_xml(p_text TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM XMLPARSE(DOCUMENT p_text);
    RETURN TRUE;
EXCEPTION
    WHEN invalid_xml_document THEN RETURN FALSE;
    WHEN others THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Find all invalid rows in a staging table
SELECT id, raw_xml
FROM import_staging
WHERE NOT is_valid_xml(raw_xml);

-- Safe bulk insert — skip invalid rows and log them
INSERT INTO documents (xml_content)
SELECT XMLPARSE(DOCUMENT raw_xml)
FROM import_staging
WHERE is_valid_xml(raw_xml);

Prevention Tips

1. Always use XML builder libraries, never string concatenation.
Generate XML using dedicated libraries (Python's lxml, Java's JAXB, etc.) or PostgreSQL's built-in functions (xmlelement(), xmlforest(), xmlagg()). This guarantees proper escaping and structure automatically.

2. Enforce validity at the schema level.

-- Using the native xml type (validates on insert automatically)
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content XML  -- PostgreSQL validates XML on every INSERT/UPDATE
);

-- Or add a CHECK constraint on TEXT columns
ALTER TABLE raw_imports
ADD CONSTRAINT chk_xml_valid CHECK (is_valid_xml(raw_xml_column));

Using the native xml data type is the single most effective prevention — PostgreSQL validates every value on write, so invalid documents never reach your data.

Related Errors

2200N (invalid_xml_content): Same family, triggered in XMLPARSE(CONTENT ...) mode.
22021 (character_not_in_repertoire): Fires when the XML contains characters unsupported by the current database encoding.
42804 (datatype_mismatch): Can appear when casting incompatible types to xml.

📖 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