How to Use XML Data Types in PostgreSQL

XML has fallen out of fashion compared to JSON for most new application development, but it’s still very much alive in enterprise integrations, legacy system interchange formats, SOAP-based APIs, government and financial data standards, and countless configuration formats. PostgreSQL has a dedicated xml data type built to handle exactly this reality — storing well-formed XML documents with real validation, plus a genuine set of query functions for pulling data out of them. This article covers the syntax, functions, indexing, and practical patterns for working with XML in PostgreSQL.

What Is the XML Data Type?

The xml type stores XML data with one meaningful guarantee that a plain text column can’t give you: PostgreSQL validates that the value is well-formed XML at insert time (assuming your PostgreSQL build was compiled with XML support, which is standard in virtually all modern distributions and cloud-managed Postgres offerings).

SELECT xmlparse(document '<order><id>1001</id><total>149.99</total></order>');

If the input isn’t well-formed XML, Postgres rejects it immediately:

SELECT xmlparse(document '<order><id>1001</id>');
-- ERROR:  invalid XML document
-- (missing closing tag)

Basic Table Setup

CREATE TABLE order_documents (
    order_id serial PRIMARY KEY,
    order_data xml NOT NULL,
    received_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO order_documents (order_data)
VALUES (xmlparse(document '<order><id>1001</id><customer>Jane Doe</customer><total>149.99</total></order>'));

You can also cast a text string directly, which implicitly parses and validates it:

INSERT INTO order_documents (order_data)
VALUES ('<order><id>1002</id><customer>Bob Smith</customer><total>89.50</total></order>'::xml);

Documents vs. Content

XML values in Postgres can be parsed as either a document (must have exactly one root element, matching the strict XML document standard) or content (a fragment that can have multiple top-level nodes, text, or no single root):

SELECT xmlparse(document '<root><a/><b/></root>');   -- valid document
SELECT xmlparse(content '<a/><b/>');                   -- valid content (multiple top-level nodes)
SELECT xmlparse(document '<a/><b/>');                  -- ERROR: not a valid document (no single root)

If you’re storing complete documents (a full order, a full invoice), use document. If you’re storing fragments that might not have a single root, use content.

Building XML with Functions

Rather than hand-assembling XML strings (which is error-prone and vulnerable to injection-style bugs from unescaped special characters), Postgres gives you dedicated construction functions:

SELECT xmlelement(name "order",
    xmlelement(name "id", 1001),
    xmlelement(name "customer", 'Jane Doe'),
    xmlelement(name "total", 149.99)
);

This produces:

<order><id>1001</id><customer>Jane Doe</customer><total>149.99</total></order>

Adding attributes:

SELECT xmlelement(name "order",
    xmlattributes(1001 AS "id", 'confirmed' AS "status"),
    xmlelement(name "customer", 'Jane Doe')
);

Result:

<order id="1001" status="confirmed"><customer>Jane Doe</customer></order>

Building a document directly from a query — genuinely one of the more powerful patterns here — using xmlagg() to aggregate multiple rows into a single XML structure:

SELECT xmlelement(name "orders",
    xmlagg(
        xmlelement(name "order",
            xmlattributes(order_id AS "id"),
            xmlelement(name "customer", customer_name)
        )
    )
)
FROM orders;

This is a clean way to generate an XML export of query results directly from SQL, without needing an application-layer XML serialization step.

Extracting Data with xpath()

This is where the xml type earns its keep for real applications — being able to query into the structure rather than treating it as an opaque blob.

SELECT xpath('/order/customer/text()', order_data)
FROM order_documents
WHERE order_id = 1;

xpath() returns an array of matched XML nodes, so you’ll often want to pull the first (and usually only) match and cast it:

SELECT (xpath('/order/customer/text()', order_data))[1]::text AS customer_name
FROM order_documents;

Extracting an attribute value:

SELECT (xpath('/order/@status', order_data))[1]::text AS status
FROM order_documents;

Filtering rows based on XML content:

SELECT order_id
FROM order_documents
WHERE (xpath('/order/customer/text()', order_data))[1]::text = 'Jane Doe';

Handling XML Namespaces

Real-world XML documents very frequently use namespaces, and xpath() requires you to pass namespace mappings explicitly as a third argument:

SELECT xpath(
    '/ns:order/ns:total/text()',
    order_data,
    ARRAY[ARRAY['ns', 'http://example.com/orders']]
)
FROM order_documents;

Forgetting this is one of the most common reasons xpath() queries silently return empty results against namespaced documents — the path simply doesn’t match without the correct namespace binding, and Postgres won’t warn you about it.

Converting Between XML and Relational Data

From Relational to XML

Beyond manual construction with xmlelement, Postgres provides convenience functions for generating XML representations of query results directly:

SELECT table_to_xml('orders', true, false, '');

This generates a full XML representation of the entire orders table — genuinely useful for quick exports or simple system-to-system data exchange without writing custom serialization code.

From XML to Relational (Shredding)

Turning XML data into proper rows and columns — often called “shredding” — is typically done with xmltable(), which is by far the most practical tool for working with XML data at scale in modern PostgreSQL:

SELECT *
FROM xmltable(
    '/order' PASSING (SELECT order_data FROM order_documents WHERE order_id = 1)
    COLUMNS
        id integer PATH 'id',
        customer text PATH 'customer',
        total numeric PATH 'total'
);

xmltable() is dramatically easier to work with than chaining multiple xpath() calls, especially once you’re dealing with nested or repeating elements:

-- Given XML with multiple <item> elements inside an <order>
SELECT o.order_id, items.product_name, items.quantity
FROM order_documents o,
     xmltable(
        '/order/items/item' PASSING o.order_data
        COLUMNS
            product_name text PATH 'product',
            quantity integer PATH 'quantity'
     ) AS items;

This pattern — join against xmltable() as if it were a regular table — is the standard, idiomatic way to shred nested XML structures into normal relational rows in modern PostgreSQL, and it’s worth learning well if you’re working with XML regularly.

Indexing XML Data

The xml type itself doesn’t support direct B-tree or GIN indexing the way jsonb does. If you need efficient lookups on values inside XML documents, the standard approach is to create a functional (expression) index on the specific xpath() extraction you query most often:

CREATE INDEX idx_order_customer ON order_documents (
    ((xpath('/order/customer/text()', order_data))[1]::text)
);

This lets equality queries against that specific extracted value use an index, rather than re-parsing and re-evaluating the XPath expression against every row on every query.

XML vs. JSON/JSONB: Which Should You Use?

For genuinely new application development in PostgreSQL, jsonb is almost always the better default choice — better indexing support (native GIN indexes without needing functional indexes), a more compact binary storage format, and broader tooling support across the modern web stack.

You should reach for xml specifically when:

• You’re integrating with existing systems, APIs, or standards that are XML-native (SOAP services, many enterprise/government/financial data exchange formats, certain legacy ERP or EDI systems).
• You need XML-specific validation or transformation features — well-formedness checking, namespace handling, XSLT-adjacent processing patterns.
• You’re storing documents that need to remain valid XML for downstream systems that expect it, rather than data you control the shape of end-to-end.

If you’re building something greenfield with no XML integration requirement, there’s little reason to reach for the xml type over jsonb — but if XML is already the format you’re receiving or need to produce, Postgres’s native support means you don’t need to bolt on an external library just to validate and query it.

Practical Use Cases

1. Storing and Querying Incoming XML Payloads from External Systems

Webhooks, SOAP responses, EDI documents, and similar integration payloads can be stored as-is, validated automatically on insert, and queried with xpath()/xmltable() without a separate parsing step in application code.

2. Generating XML Exports for Downstream Systems

Using xmlelement, xmlagg, and table_to_xml to produce XML output directly from SQL queries, for systems that require XML as their exchange format.

3. Auditing and Archiving Structured Documents

Storing full request/response payloads from XML-based APIs for auditing purposes, while still being able to query specific fields inside them when needed for troubleshooting or reporting.

Troubleshooting Common Issues

“Invalid XML document” errors on insert. The value isn’t well-formed — check for unclosed tags, invalid characters, or multiple root elements if you’re using document mode instead of content.

xpath() returning an empty array unexpectedly. By far the most common cause is a missing namespace binding — verify whether the source document declares a default or prefixed namespace, and pass the corresponding mapping explicitly as the third argument to xpath().

Slow queries filtering on values inside XML documents. As covered above, add a functional index on the specific xpath() expression you’re filtering by — without one, every query re-parses and re-evaluates the XPath against every row.

Confusing document and content parsing modes. If your data sometimes has a single root and sometimes doesn’t, standardize on content parsing, or ensure upstream data always wraps fragments in a consistent root element before insertion.

Special character and encoding issues. When constructing XML manually via string concatenation (rather than xmlelement), unescaped characters like &, <, and > in the data will produce invalid XML. Always prefer the xmlelement/xmlattributes construction functions over manual string building — they handle escaping correctly for you.

Best Practices

• Prefer jsonb for new, greenfield application data unless you have a genuine XML integration requirement.
• Use xmlelement/xmlattributes/xmlagg for construction rather than hand-built strings, to avoid escaping bugs.
• Always account for namespaces explicitly when writing xpath() queries against real-world XML — don’t assume a namespace-free document.
• Reach for xmltable() over chained xpath() calls once you’re dealing with repeating or nested elements — it’s dramatically easier to read and maintain.
• Add functional indexes on any xpath() expression you filter or join on regularly.

Validating XML Structure Beyond Well-Formedness

Well-formedness (matching tags, proper nesting, valid characters) is checked automatically by the xml type, but that’s a much weaker guarantee than actually conforming to a specific schema (an XSD or DTD). PostgreSQL’s built-in XML support doesn’t include schema validation against an XSD out of the box — if you need genuine schema-level validation (checking that an <order> element always has exactly one <id> child of the right type, for instance), that typically needs to happen either before the data reaches Postgres, or through a combination of CHECK constraints using xpath() to enforce specific structural expectations.

ALTER TABLE order_documents
ADD CONSTRAINT order_must_have_id
CHECK (xpath_exists('/order/id', order_data));

xpath_exists() is a convenient boolean-returning variant of xpath() specifically suited to this kind of structural constraint — it returns true or false rather than an array of matched nodes, which makes it a natural fit for CHECK constraints and conditional logic.

Transforming XML with SQL

While Postgres doesn’t include a full XSLT processor built in, you can perform meaningful structural transformations directly in SQL by combining xmltable() for extraction with xmlelement()/xmlagg() for reconstruction — effectively hand-rolling a transformation pipeline for cases that don’t warrant pulling in an external XSLT engine.

-- Transform an incoming <order> document into a simplified <summary> document
SELECT xmlelement(name "summary",
    xmlelement(name "order_id", extracted.id),
    xmlelement(name "customer_name", extracted.customer),
    xmlelement(name "grand_total", extracted.total)
)
FROM order_documents,
     xmltable(
        '/order' PASSING order_data
        COLUMNS
            id integer PATH 'id',
            customer text PATH 'customer',
            total numeric PATH 'total'
     ) AS extracted;

This pattern — xmltable() to shred, then xmlelement()/xmlagg() to rebuild in a different shape — covers a genuinely large share of the practical “transform this XML into a different XML structure” needs that come up in integration work, without requiring a separate XSLT toolchain for reasonably simple transformations.

Working with CDATA and Mixed Content

Real-world XML documents frequently include CDATA sections (raw, unescaped text blocks) or mixed content (text interspersed with child elements), both of which deserve a bit of extra care when extracting data.

SELECT xmlparse(document '<note><![CDATA[Some <raw> & unescaped text]]></note>');

When extracting text that might include CDATA sections via xpath(), the content comes through correctly as the underlying text value — Postgres handles the CDATA unwrapping transparently as part of parsing, so you don’t need any special-case handling in your extraction queries beyond the normal text() node selection already shown earlier in this article.

A Complete Integration Example

Bringing several pieces together, here’s a more complete example resembling a genuine integration scenario — receiving XML orders from an external partner system, validating them, storing them, and later generating an XML acknowledgment:

CREATE TABLE partner_orders (
    order_id serial PRIMARY KEY,
    raw_payload xml NOT NULL,
    status text NOT NULL DEFAULT 'received',
    received_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION ingest_partner_order(p_payload text)
RETURNS integer AS $$
DECLARE
    new_id integer;
    parsed xml;
BEGIN
    parsed := xmlparse(document p_payload);

    IF NOT xpath_exists('/order/id', parsed) THEN
        RAISE EXCEPTION 'Order payload missing required id element';
    END IF;

    INSERT INTO partner_orders (raw_payload) VALUES (parsed)
    RETURNING order_id INTO new_id;

    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION generate_acknowledgment(p_order_id integer)
RETURNS xml AS $$
BEGIN
    RETURN xmlelement(name "acknowledgment",
        xmlelement(name "order_id", p_order_id),
        xmlelement(name "status", 'received'),
        xmlelement(name "timestamp", now())
    );
END;
$$ LANGUAGE plpgsql;

SELECT ingest_partner_order('<order><id>5001</id><customer>Acme</customer></order>');
SELECT generate_acknowledgment(1);

This kind of ingest-validate-acknowledge flow, entirely inside the database, is a genuinely common pattern for systems that need to interoperate with XML-native partners without standing up a separate XML processing service just for basic validation and transformation needs.

Escaping and Special Characters

When you do need to build XML content from raw strings rather than using the construction functions, PostgreSQL provides xmlescape behavior implicitly through the construction functions themselves — but it’s worth understanding what happens if you bypass them:

-- Safe: xmlelement handles escaping automatically
SELECT xmlelement(name "note", 'Terms & Conditions apply');
-- <note>Terms &amp; Conditions apply</note>

-- Risky: manual string concatenation does not escape special characters
SELECT ('<note>' || 'Terms & Conditions apply' || '</note>')::xml;
-- ERROR or malformed XML, depending on the exact content

This is worth internalizing as a hard rule rather than a suggestion: any time XML content includes user-supplied or otherwise untrusted text, build it with xmlelement/xmlattributes rather than string concatenation, precisely because the construction functions handle escaping of characters like &, <, and > automatically and correctly, while manual concatenation leaves you fully responsible for getting that escaping right yourself.

Checking XML Well-Formedness Without Storing

Sometimes you just need to validate a document without committing it anywhere — useful for a pre-flight check in an ingestion pipeline before deciding whether to proceed:

CREATE OR REPLACE FUNCTION is_well_formed_xml(p_text text)
RETURNS boolean AS $$
BEGIN
    PERFORM xmlparse(document p_text);
    RETURN true;
EXCEPTION WHEN OTHERS THEN
    RETURN false;
END;
$$ LANGUAGE plpgsql;

SELECT is_well_formed_xml('<order><id>1</id></order>');  -- true
SELECT is_well_formed_xml('<order><id>1</id>');            -- false

Wrapping the parse attempt in an exception handler like this turns what would otherwise be a hard error into a simple boolean check, which is genuinely useful for validating a batch of incoming documents and routing the malformed ones to an error queue rather than letting one bad document abort an entire ingestion job.

Wrapping Up

The xml type isn’t something most new PostgreSQL projects reach for by default anymore, but for the very real world of enterprise integrations, legacy interchange formats, and XML-native external systems, it’s a genuinely capable, well-thought-out piece of the database — automatic well-formedness validation, real XPath querying, and clean tools for both generating and shredding XML directly in SQL. If XML shows up anywhere in your data pipeline, PostgreSQL’s native support means you can handle it properly inside the database rather than bolting on external parsing libraries in every service that touches it.