PostgreSQL 2200S Error: Causes and Solutions Complete Guide

PostgreSQL Error 2200S: invalid xml comment

PostgreSQL error 2200S (invalid_xml_comment) is thrown when the XML parser encounters a malformed XML comment that violates the W3C XML specification. The most common trigger is embedding a double-hyphen (--) inside an XML comment body, or ending a comment with a trailing hyphen before the closing -->. This error typically surfaces when using XMLCOMMENT(), XMLPARSE(), or inserting data into xml-typed columns.

---

Top 3 Causes

1. Double Hyphen (--) Inside XML Comment Body

The XML standard explicitly forbids -- inside comment content. Unlike SQL where -- denotes a comment, in XML it is a reserved sequence that confuses the parser.

-- This will FAIL
SELECT XMLCOMMENT('this is an invalid -- comment');
-- ERROR:  invalid xml comment

-- This works fine
SELECT XMLCOMMENT('this is a valid comment');
-- Result: <!--this is a valid comment-->

-- Safe workaround: replace -- with - -
SELECT XMLCOMMENT(REPLACE('user input -- value', '--', '- -'));
-- Result: <!--user input - - value-->

2. Passing Invalid Strings to XMLCOMMENT() Function

The XMLCOMMENT() function validates its input against the XML standard internally. Passing unsanitized user input directly into this function is a common mistake, especially when the input may contain double hyphens or end with a single hyphen.

-- String ending with hyphen also causes issues
SELECT XMLCOMMENT('trailing hyphen-');
-- ERROR: invalid xml comment

-- Fix: append a space if the string ends with a hyphen
CREATE OR REPLACE FUNCTION safe_xmlcomment(p_text TEXT)
RETURNS XML AS $$
DECLARE
    v_safe TEXT := REPLACE(p_text, '--', '- -');
BEGIN
    IF v_safe LIKE '%-' THEN
        v_safe := v_safe || ' ';
    END IF;
    RETURN XMLCOMMENT(v_safe);
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT safe_xmlcomment('user data -- ends with hyphen-');
-- Result: <!--user data - - ends with hyphen- -->

3. Inserting XML from External Systems Without Validation

XML generated by legacy systems, third-party APIs, or other databases may contain non-standard comment syntax. Inserting such data directly into a PostgreSQL xml column without prior validation will trigger this error.

-- Attempting to insert invalid XML from an external source
INSERT INTO xml_documents (doc_name, content)
VALUES (
    'external_data',
    XMLPARSE(DOCUMENT
        '<?xml version="1.0"?>
         <root><!-- bad -- comment --><item>data</item></root>'
    )
);
-- ERROR: invalid xml comment

-- Safe insert with exception handling
DO $$
BEGIN
    INSERT INTO xml_documents (doc_name, content)
    VALUES (
        'safe_data',
        XMLPARSE(DOCUMENT
            '<?xml version="1.0"?>
             <root><!--good comment--><item>data</item></root>'
        )
    );
EXCEPTION
    WHEN invalid_xml_comment THEN
        RAISE NOTICE 'Skipped: invalid XML comment in source data.';
END;
$$;

---

Quick Fix Solutions

-- 1. Use REPLACE to sanitize before passing to XMLCOMMENT
SELECT XMLCOMMENT(REPLACE(user_input_column, '--', '- -'))
FROM your_table;

-- 2. Use regexp_replace for more robust sanitization
SELECT XMLCOMMENT(
    regexp_replace(user_input_column, '-{2,}', '- -', 'g')
)
FROM your_table;

-- 3. Catch the error gracefully in a PL/pgSQL block
DO $$
BEGIN
    PERFORM XMLCOMMENT('some -- problematic text');
EXCEPTION
    WHEN invalid_xml_comment THEN
        RAISE WARNING 'Invalid XML comment detected, skipping.';
END;
$$;

---

Prevention Tips

1. Sanitize XML input at the application layer before it reaches the database. Build a shared utility in your application code (Python, Java, Node.js, etc.) that strips or replaces -- sequences and trailing hyphens in any XML comment content. Catching this upstream prevents the error from ever reaching PostgreSQL.

2. Use a BEFORE INSERT/UPDATE trigger to enforce XML comment safety at the database level. This acts as a last line of defense for any code path that bypasses application-level validation.

CREATE OR REPLACE FUNCTION sanitize_xml_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.content IS NOT NULL THEN
        BEGIN
            NEW.content := XMLPARSE(DOCUMENT
                regexp_replace(NEW.content::TEXT, '--(?!>)', '- -', 'g')
            );
        EXCEPTION WHEN OTHERS THEN
            RAISE EXCEPTION 'Invalid XML comment format in input data.';
        END;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_xml_sanitize
BEFORE INSERT OR UPDATE ON xml_documents
FOR EACH ROW EXECUTE FUNCTION sanitize_xml_trigger();

---

Related Errors

Error Code	Name	Description
2200N	invalid_xml_content	General invalid XML content structure
2200M	invalid_xml_document	Malformed XML document (missing root, bad declaration)
2200T	invalid_xml_processing_instruction	Bad XML processing instruction format
22000	data_exception	Parent class for all data-related exceptions

---

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