PostgreSQL 2200M Error: Causes and Solutions Complete Guide

PostgreSQL Error 2200M: invalid xml document

PostgreSQL error 2200M: invalid xml document is raised when an XML value passed to XMLPARSE(DOCUMENT ...) or inserted into an xml-typed column does not conform to the W3C XML specification for a well-formed document. Unlike 2200N: invalid xml content, this error specifically targets document-level structural violations, such as missing or multiple root elements. It commonly appears during data migrations, API integrations, or when dynamically building XML strings in application code.

Top 3 Causes & SQL Examples

1. Multiple or Missing Root Elements

An XML document must have exactly one root element. Passing fragments or multiple top-level elements will immediately trigger this error.

-- Bad: multiple root elements
SELECT XMLPARSE(DOCUMENT '<item>A</item><item>B</item>');
-- ERROR:  invalid xml document

-- Bad: no root element at all
SELECT XMLPARSE(DOCUMENT 'just some text');
-- ERROR:  invalid xml document

-- Good: wrap everything in a single root
SELECT XMLPARSE(DOCUMENT '<root><item>A</item><item>B</item></root>');

2. Unescaped Special Characters or Unclosed Tags

Characters like &, <, and > must be escaped as XML entities. Unclosed tags are also a common culprit when XML is built by string concatenation.

-- Bad: unescaped ampersand
SELECT XMLPARSE(DOCUMENT '<root><price>10 & 20</price></root>');
-- ERROR:  invalid xml document

-- Bad: unclosed tag
SELECT XMLPARSE(DOCUMENT '<root><item>value</root>');
-- ERROR:  invalid xml document

-- Good: use PostgreSQL built-in XML functions (auto-escaping)
SELECT XMLELEMENT(NAME "root",
    XMLELEMENT(NAME "price", '10 & 20')
);

-- Good: manually escape entities
SELECT XMLPARSE(DOCUMENT '<root><price>10 &amp; 20</price></root>');

3. Encoding Declaration Mismatch

An <?xml version="1.0" encoding="..."> declaration that does not match the actual database encoding will cause parsing to fail.

-- Check your database encoding first
SHOW server_encoding;
-- e.g., UTF8

-- Bad: declared encoding differs from DB encoding
SELECT XMLPARSE(DOCUMENT
    '<?xml version="1.0" encoding="EUC-KR"?><root>data</root>'
);
-- May raise: invalid xml document

-- Good: match the declaration to your DB encoding
SELECT XMLPARSE(DOCUMENT
    '<?xml version="1.0" encoding="UTF-8"?><root>data</root>'
);

-- Good: omit the declaration entirely
SELECT XMLPARSE(DOCUMENT '<root>data</root>');

-- Utility function to strip problematic XML declarations
CREATE OR REPLACE FUNCTION strip_xml_declaration(p_xml TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN regexp_replace(p_xml, '^\s*<\?xml[^?]*\?>\s*', '', 'i');
END;
$$ LANGUAGE plpgsql;

Quick Fix Solutions

Use this validation helper before inserting any XML data:

-- Safe validation wrapper
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 invalid_xml_content  THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Filter out bad rows before bulk insert
INSERT INTO xml_store (payload)
SELECT XMLPARSE(DOCUMENT raw_xml)
FROM staging_table
WHERE is_valid_xml(raw_xml);

Prevention Tips

Always use PostgreSQL's native XML builder functions (XMLELEMENT, XMLFOREST, XMLAGG) instead of string concatenation. These functions handle escaping and structure automatically, eliminating the most common sources of malformed documents.
Add a CHECK constraint on XML columns and validate incoming data at the pipeline boundary before it reaches the database.

-- Add constraint using the validation function
ALTER TABLE xml_store
ADD CONSTRAINT chk_valid_xml_doc
CHECK (is_valid_xml(payload::text));

-- Prefer the native xml type over text for automatic parse-time validation
CREATE TABLE documents (
    id      SERIAL PRIMARY KEY,
    content XML NOT NULL  -- PostgreSQL validates on insert automatically
);

Related Errors

Code	Name	Notes
`2200N`	invalid xml content	Triggered by `XMLPARSE(CONTENT ...)` with structural issues
`22000`	data exception	Parent category for all data-related exceptions
`42804`	datatype mismatch	Occurs when a non-XML value is cast to the `xml` type incorrectly

📖 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