DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 2200M Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

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>');
Enter fullscreen mode Exit fullscreen mode

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>');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

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

  2. 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
);
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)