DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 2200N Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2200N: invalid xml content

PostgreSQL error 2200N: invalid xml content occurs when you attempt to insert or process malformed XML data into an XML type column or pass invalid XML to XML-related functions. PostgreSQL strictly validates XML data against the W3C XML standard, and any deviation — from mismatched tags to illegal characters — triggers this error. It most commonly appears when ingesting XML from external APIs, legacy systems, or dynamically building XML strings in application code.

Top 3 Causes & SQL Examples

1. Mismatched or Improperly Nested Tags

XML requires every opening tag to have a matching closing tag in the correct order.

-- This will fail: tags are improperly nested
INSERT INTO orders (id, xml_data)
VALUES (1, XMLPARSE(DOCUMENT '<order><item>Apple</order></item>'));
-- ERROR:  invalid xml content

-- Correct version
INSERT INTO orders (id, xml_data)
VALUES (1, XMLPARSE(DOCUMENT '<order><item>Apple</item></order>'));
Enter fullscreen mode Exit fullscreen mode

2. Unescaped Special Characters

Characters like &, <, and > must be escaped as XML entities (&amp;, &lt;, &gt;). Passing them raw inside XML text nodes will break parsing immediately.

-- This will fail: unescaped ampersand
SELECT XMLPARSE(DOCUMENT '<company><name>AT&T</name></company>');
-- ERROR:  invalid xml content

-- Fix 1: Use xmlelement() — it escapes automatically
SELECT xmlelement(name company,
           xmlelement(name name, 'AT&T')
       );
-- Result: <company><name>AT&amp;T</name></company>

-- Fix 2: Manual escape before parsing
SELECT XMLPARSE(DOCUMENT
    replace(raw_xml, '&', '&amp;')
)
FROM staging_table
WHERE id = 1;
Enter fullscreen mode Exit fullscreen mode

3. Multiple Root Elements or Missing Root

A valid XML document must have exactly one root element. Concatenating XML fragments without a wrapper is a very common mistake.

-- This will fail: two root elements
SELECT XMLPARSE(DOCUMENT '<item>A</item><item>B</item>');
-- ERROR:  invalid xml content

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

-- Safely aggregate multiple rows into one XML document
WITH fragments AS (
    SELECT '<item>' || product_name || '</item>' AS frag
    FROM products
    WHERE category = 'fruit'
)
SELECT XMLPARSE(DOCUMENT
    '<items>' || string_agg(frag, '') || '</items>'
)
FROM fragments;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Create a validation helper function to identify bad rows before they cause errors in production:

-- 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_content THEN
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Find all invalid XML rows in a staging table
SELECT id, raw_xml
FROM staging_xml_data
WHERE is_valid_xml(raw_xml) = FALSE;

-- Safe load: only move valid rows to production
INSERT INTO production_table (id, xml_data)
SELECT id, XMLPARSE(DOCUMENT raw_xml)
FROM staging_xml_data
WHERE is_valid_xml(raw_xml) = TRUE;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Use PostgreSQL's built-in XML functions instead of string concatenation.
Functions like xmlelement(), xmlforest(), and xmlagg() automatically escape special characters and guarantee well-formed output, eliminating the most common source of this error.

-- Preferred: built-in functions handle escaping for you
SELECT xmlelement(name report,
           xmlattributes(NOW() AS generated_at),
           xmlforest(
               customer_name AS customer,
               total_amount  AS total
           )
       )
FROM orders;
Enter fullscreen mode Exit fullscreen mode

2. Validate XML at the staging layer before promoting to production.
Always land raw XML into a TEXT staging column first, run is_valid_xml() batch checks, and only promote verified data to your XML typed production columns. This two-stage pipeline prevents invalid data from ever reaching live tables and makes debugging far easier.

Related Errors

  • 2200M (invalid XML document) — Similar error; the document structure is recognized but content violates XML rules.
  • 22000 (data exception) — Parent error class for all XML and data-type violations.
  • 42804 (datatype mismatch) — Raised when inserting TEXT into an XML column without an explicit cast.

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