XML

Introduction to the PostgreSQL XML data type

The XML data type stores well-formed XML documents directly in the database. PostgreSQL validates XML on insert and provides built-in functions for parsing and querying with XPath.

Declare an XML column with:

column_name XML

Benefits include type safety (PostgreSQL rejects malformed XML) and built-in XPath support via the xpath() function.

Creating a table and inserting XML data

CREATE TABLE person (
  id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  info XML
);

-- Insert a full XML document using XMLPARSE
INSERT INTO person (info) VALUES (
  XMLPARSE(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?>
  <person>
    <name>John Doe</name>
    <age>35</age>
    <city>San Francisco</city>
  </person>')
);

INSERT INTO person (info) VALUES
  (XMLPARSE(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?><person><name>Jane Doe</name><age>30</age><city>San Francisco</city></person>')),
  (XMLPARSE(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?><person><name>John Smith</name><age>40</age><city>New York</city></person>')),
  (XMLPARSE(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?><person><name>Alice Johnson</name><age>30</age><city>Los Angeles</city></person>'));

Querying XML with xpath()

-- Extract names as an array of XML values
SELECT xpath('/person/name/text()', info) AS name
FROM person;
-- {"John Doe"}, {"Jane Doe"}, {"John Smith"}, {"Alice Johnson"}

-- Extract the first element as text
SELECT (xpath('/person/name/text()', info))[1]::text AS name
FROM person;
-- John Doe / Jane Doe / John Smith / Alice Johnson

-- Extract age as an integer (XML -> text -> integer, no direct cast)
SELECT (xpath('/person/age/text()', info))[1]::text::integer AS age
FROM person;

Extracting multiple fields at once

SELECT
  (xpath('/person/name/text()', info))[1]::text AS name,
  (xpath('/person/age/text()',  info))[1]::text::integer AS age,
  (xpath('/person/city/text()', info))[1]::text AS city
FROM person;

-- Output:
-- name          | age |     city
-- --------------+-----+---------------
-- John Doe      |  35 | San Francisco
-- Jane Doe      |  30 | San Francisco
-- John Smith    |  40 | New York
-- Alice Johnson |  30 | Los Angeles

Filtering rows by XML content

SELECT * FROM person
WHERE (xpath('/person/name/text()', info))[1]::text = 'Jane Doe';

Creating an index on XML data

XPath queries on large tables are slow without an index. Create an expression index:

CREATE INDEX person_name
ON person USING BTREE (cast(xpath('/person/name', info) AS text[]));

-- Query using the same expression to trigger the index
EXPLAIN ANALYZE
SELECT * FROM person
WHERE cast(xpath('/person/name', info) AS text[]) = '{<name>Jane Doe</name>}';

Production tips

• For new designs, prefer JSONB over XML — it has richer operator support, better indexing, and is more commonly used in modern applications.
• Use the XML type when integrating with systems that produce or require XML (legacy enterprise APIs, SOAP services, XSLT pipelines).
• Always cast extracted XML values to text before casting to another type — PostgreSQL does not support direct casting from XML to integer or date.
• Create expression indexes on frequently queried XPath expressions to avoid full table scans.

Reference: PostgreSQL documentation — XML type.