PostgreSQL Error 2200S: invalid xml comment
PostgreSQL error 2200S (invalid_xml_comment) is raised when an XML comment embedded in XML data violates the W3C XML 1.0 specification. This most commonly occurs when using XML-related functions such as xmlcomment(), XMLPARSE(), or when inserting data into an XML type column. Understanding the exact rules governing XML comments will save you significant debugging time in production environments.
Top 3 Causes
1. Double Hyphens (--) Inside an XML Comment
The XML 1.0 spec explicitly forbids the sequence -- anywhere inside an XML comment body. This catches many developers off guard, especially those accustomed to SQL-style -- comments.
-- This will FAIL: double hyphen inside comment
SELECT xmlcomment('this is -- invalid');
-- ERROR: invalid xml comment (SQLSTATE 2200S)
-- This works: replace '--' with '- -'
SELECT xmlcomment('this is - - valid');
-- Safe dynamic comment generation
SELECT xmlcomment(
replace(user_input_value, '--', '- -')
)
FROM (SELECT 'status -- pending' AS user_input_value) t;
2. Malformed Comment Closing Tag
An XML comment must close with exactly -->. Using --->, ---->, or omitting the closing tag entirely causes the parser to throw 2200S.
-- FAIL: extra hyphen before closing '>'
SELECT XMLPARSE(DOCUMENT
'<?xml version="1.0"?><!--- bad close --->
<root/>');
-- ERROR: invalid xml comment
-- PASS: standard well-formed comment
SELECT XMLPARSE(DOCUMENT
'<?xml version="1.0"?><!-- good comment -->
<root><item>data</item></root>');
-- Inserting a valid XML document with a comment
INSERT INTO reports (doc)
VALUES (XMLPARSE(DOCUMENT
'<?xml version="1.0" encoding="UTF-8"?>
<!-- Generated: 2024-01-15 -->
<report><title>Q1</title></report>'));
3. Unsanitized User Input Injected into XML Comments
When raw user input or external API data is interpolated directly into XML comment strings, any embedded -- sequence will trigger this error. This is a common bug in ETL pipelines and dynamic XML builders.
-- Helper function to sanitize text before using in XML comments
CREATE OR REPLACE FUNCTION safe_xml_comment(p_text TEXT)
RETURNS XML
LANGUAGE plpgsql AS $$
BEGIN
-- Strip double hyphens and trailing hyphens
RETURN xmlcomment(
rtrim(replace(p_text, '--', '- -'), '-')
);
END;
$$;
-- Usage
SELECT safe_xml_comment('user note: value--123');
-- Returns: <!-- user note: value- -123 -->
-- Catch the error gracefully in PL/pgSQL
DO $$
BEGIN
PERFORM xmlcomment('bad--input');
EXCEPTION
WHEN invalid_xml_comment THEN
RAISE NOTICE 'Caught SQLSTATE %, cleaning input...', SQLSTATE;
END;
$$;
Quick Fix Solutions
-- 1. Validate existing XML column for problematic comments
SELECT id
FROM xml_documents
WHERE content::TEXT ~ '--(?!>)';
-- 2. Bulk-fix stored XML by replacing double hyphens
UPDATE xml_documents
SET content = XMLPARSE(DOCUMENT
replace(content::TEXT, '-->', ' -->') -- adjust as needed
)
WHERE content::TEXT ~ '--(?!>)';
-- 3. Use xmlcomment() instead of manual string concat
-- BAD:
SELECT ('<!-- my comment -->'::XML);
-- GOOD:
SELECT xmlcomment('my comment');
Prevention Tips
1. Always use xmlcomment() for generating XML comments. Never build XML comment strings through raw concatenation. PostgreSQL's built-in xmlcomment() function still requires valid input, but pairing it with a sanitizer function makes the intent explicit and auditable.
2. Add a BEFORE INSERT/UPDATE trigger on XML columns. Let the database enforce XML validity as a last line of defense, independent of application logic.
CREATE OR REPLACE FUNCTION trg_validate_xml()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
-- Force re-parse to catch any malformed XML including bad comments
PERFORM XMLPARSE(DOCUMENT NEW.content::TEXT);
RETURN NEW;
EXCEPTION
WHEN invalid_xml_comment THEN
RAISE EXCEPTION 'Invalid XML comment in input. SQLSTATE: 2200S';
END;
$$;
CREATE TRIGGER validate_xml_before_write
BEFORE INSERT OR UPDATE ON xml_documents
FOR EACH ROW EXECUTE FUNCTION trg_validate_xml();
Related Errors
| SQLSTATE | Name | When It Occurs |
|---|---|---|
| 2200M | invalid_xml_document | XML is not well-formed at the document level |
| 2200N | invalid_xml_content | XML content (not comment) fails validation |
| 2200T | invalid_xml_processing_instruction | Malformed <?target data?> PI node |
| 22000 | data_exception | Parent class for all XML and data errors |
📖 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.