🏷

PostgreSQL XML Functions

Complete reference for PostgreSQL XML functions covering XML generation, XPath querying, validation, and relational shredding with xmltable(). Every function includes syntax, examples, and notes on the xml data type. Updated for PostgreSQL 16.

7 functions

Jump to function

xmlagg xmlconcat xmlelement xmlforest XMLTABLE xpath xpath_exists

What are PostgreSQL XML Functions?

PostgreSQL XML functions support storing, querying, and generating XML data natively. The xmlelement() and xmlforest() functions build XML from query results, xpath() evaluates XPath expressions against an XML value, and xmltable() shreds XML documents into relational rows. XML support in PostgreSQL requires the server to be built with libxml.

xmlagg

PG 8.3+→ xml

Aggregates XML values into a concatenated XML sequence. Equivalent to string_agg for XML.

DeveloperData Eng

Signature

xmlagg ( xml [ORDER BY sort_expression] ) → xml

Parameters

Parameter	Type	Description
xml_expression	xml	XML value to aggregate

Examples

sql

SELECT xmlagg(xmlelement(NAME item, name) ORDER BY name) FROM products;

→<item>Apple</item><item>Banana</item>...

sql

SELECT xmlelement(NAME catalog, xmlagg(xmlelement(NAME product, XMLATTRIBUTES(id), name) ORDER BY id)) FROM products;

→Full product catalog XML

sql

SELECT dept_id, xmlagg(xmlelement(NAME employee, XMLATTRIBUTES(id), name) ORDER BY name) AS staff FROM employees GROUP BY dept_id;

→Per-department employee XML lists

sql

SELECT xmlelement(NAME feed, XMLATTRIBUTES('2.0' AS version), xmlagg(xmlelement(NAME entry, xmlforest(title, published_at AS pubDate, body AS content)) ORDER BY published_at DESC)) FROM posts;

→RSS-style XML feed ordered by publication date

⚠Anti-Pattern— Aggregating XML in application code instead of using xmlagg

Fetching all XML rows into application code and concatenating them in a loop wastes network bandwidth and forces the application to handle XML serialization. `xmlagg` performs the aggregation entirely in the database.

✓ Instead: Use `xmlagg` with an `ORDER BY` clause inside the aggregate to produce a deterministically ordered XML sequence without a round-trip to the application.

Combine `xmlagg` with `GROUP BY` to build XML with nested collections: outer SELECT wraps in a root element, inner `xmlagg` collects child elements per group.

example

SELECT xmlelement(NAME category, XMLATTRIBUTES(c.name), xmlagg(xmlelement(NAME product, p.name))) FROM categories c JOIN products p ON p.cat_id = c.id GROUP BY c.id, c.name;

→Products grouped under category XML elements

Common Gotchas

⚠

string_agg without ORDER BY produces non-deterministic results

string_agg(col, ',') returns results in arbitrary order unless you specify ORDER BY inside the aggregate.

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

xmlconcat

PG 8.3+→ xml

Concatenates multiple XML values into a single XML value by appending them in sequence.

DeveloperData Eng

Signature

xmlconcat ( xml [, xml ...] ) → xml

Parameters

Parameter	Type	Description
xml	xml	XML fragments to concatenate

Examples

sql

SELECT xmlconcat('<abc/>'::xml, '<bar>foo</bar>'::xml);

→<abc/><bar>foo</bar>

sql

SELECT xmlconcat(xmlelement(NAME first, first_name), xmlelement(NAME last, last_name)) FROM users;

→<first>Alice</first><last>Smith</last>

sql

SELECT xmlconcat(xmlelement(NAME name, name), CASE WHEN phone IS NOT NULL THEN xmlelement(NAME phone, phone) END) FROM contacts;

→XML with optional phone element only when present

sql

SELECT xmlconcat(xmlelement(NAME header, 'v1.0'), xmlagg(xmlelement(NAME record, id) ORDER BY id)) FROM audit_log;

→<header>v1.0</header><record>1</record><record>2</record>...

⚠Anti-Pattern— Using xmlconcat as a substitute for xmlagg when aggregating many rows

`xmlconcat` accepts a fixed argument list and cannot aggregate across rows. Wrapping it in a subquery loop or calling it row-by-row from application code is both error-prone and inefficient.

✓ Instead: Use `xmlagg` to aggregate XML across rows. Reserve `xmlconcat` for combining a small, fixed set of XML fragments within a single row expression.

Combine optional XML elements with `xmlconcat`, using NULL to skip absent elements: `xmlconcat(xmlelement(NAME req, required), CASE WHEN optional IS NOT NULL THEN xmlelement(NAME opt, optional) END)`.

example

SELECT xmlconcat(xmlelement(NAME name, name), CASE WHEN phone IS NOT NULL THEN xmlelement(NAME phone, phone) END) FROM contacts;

→XML with optional phone element only when present

xmlelement

PG 8.3+→ xml

Produces an XML element with the given name, optional attributes, and optional content.

DeveloperData Eng

Signature

xmlelement ( NAME name [, XMLATTRIBUTES ( value [ AS attname ] [, ...] )] [, content [, ...]] ) → xml

Parameters

Parameter	Type	Description
name	identifier	XML element name
attributes	expressions via XMLATTRIBUTES	Optional XML attributes
content	any	Element content (text or nested XML)

Examples

sql

SELECT xmlelement(NAME foo);

→<foo/>

sql

SELECT xmlelement(NAME greeting, 'hello world');

→<greeting>hello world</greeting>

sql

SELECT xmlelement(NAME user, XMLATTRIBUTES(id AS id, name AS name)) FROM users;

→<user id="1" name="Alice"/>

sql

SELECT xmlelement(NAME item, XMLATTRIBUTES(sku AS sku), xmlelement(NAME price, price), xmlelement(NAME stock, qty)) FROM products;

→<item sku="ABC"><price>9.99</price><stock>100</stock></item>

sql

SELECT xmlelement(NAME result, XMLATTRIBUTES(NOW() AS generated_at), xmlagg(xmlelement(NAME row, id))) FROM logs;

→<result generated_at="2024-01-01T00:00:00"><row>1</row>...</result>

⚠Anti-Pattern— Building XML via string concatenation instead of xmlelement

Concatenating strings to form XML (e.g., `'<tag>' || value || '</tag>'`) bypasses proper escaping, producing broken XML when values contain `<`, `>`, `&`, or quotes. `xmlelement` escapes content automatically.

✓ Instead: Always use `xmlelement(NAME tag, value)` — PostgreSQL handles character escaping correctly, so `&` becomes `&` and `<` becomes `<` in text nodes.

Use `xmlelement` for the outer wrapper and `xmlforest` for a set of child elements from row columns. This is cleaner than nesting multiple `xmlelement` calls.

example

SELECT xmlelement(NAME order, XMLATTRIBUTES(id), xmlforest(customer_id AS customer, total AS amount, status)) FROM orders LIMIT 3;

→<order id="1"><customer>42</customer><amount>100.00</amount><status>paid</status></order>

xmlforest

PG 8.3+→ xml

Produces an XML forest (sequence of XML elements) from a list of expressions.

DeveloperData Eng

Signature

xmlforest ( value [ AS name ] [, ...] ) → xml

Parameters

Parameter	Type	Description
value	any	Value to wrap in an XML element
name	identifier	XML element name (defaults to column name)

Examples

sql

SELECT xmlforest(first_name AS name, email AS email) FROM users LIMIT 1;

→<name>Alice</name><email>alice@example.com</email>

sql

SELECT xmlforest(42 AS id, 'hello' AS greeting);

→<id>42</id><greeting>hello</greeting>

sql

SELECT xmlelement(NAME product, xmlforest(id AS id, name AS name, price AS price)) FROM products WHERE id = 5;

→<product><id>5</id><name>Widget</name><price>9.99</price></product>

sql

SELECT xmlforest(department AS dept, COUNT(*) AS headcount) FROM employees GROUP BY department;

→<dept>Engineering</dept><headcount>12</headcount>

⚠Anti-Pattern— Using xmlforest output as a standalone XML document

A forest produces sibling XML fragments with no single root, making it invalid as a standalone XML document. Passing a bare forest to a consumer expecting well-formed XML will cause parse errors.

✓ Instead: Wrap the forest in `xmlelement(NAME root, xmlforest(...))` whenever the result must be a self-contained XML document.

A forest produces sibling XML elements without a root. Wrap with `xmlelement(NAME root, xmlforest(...))` to add a root element for valid XML output.

example

SELECT xmlelement(NAME users, xmlagg(xmlelement(NAME user, xmlforest(id, name, email)))) FROM users;

→<users><user><id>1</id><name>Alice</name>...</user></users>

XMLTABLE

PG 10+→ setof record

Converts XML data into a relational table. Evaluates XPath expressions to extract columns from XML nodes.

DeveloperData Eng

Signature

XMLTABLE ( [XMLNAMESPACES (...),] row_xpath PASSING xml COLUMNS col_name type PATH col_xpath [DEFAULT val] [, ...] ) → setof record

Parameters

Parameter	Type	Description
row_xpath	text	XPath expression that selects the rows
xml_doc	xml	Source XML document
col_name type PATH col_xpath	column definitions	Column definitions with path expressions

Examples

sql

SELECT * FROM XMLTABLE('/inventory/item' PASSING '<inventory><item><id>1</id><name>Widget</name><qty>10</qty></item></inventory>'::xml COLUMNS id integer PATH 'id', name text PATH 'name', qty integer PATH 'qty');

→

id|name  |qty
1 |Widget |10

sql

SELECT t.* FROM xml_responses r, XMLTABLE('/response/item' PASSING r.xml_data COLUMNS id bigint PATH '@id', title text PATH 'title', price numeric PATH 'price' DEFAULT 0) AS t;

→XML response rows as a typed SQL result set

sql

SELECT t.* FROM feeds f, XMLTABLE(XMLNAMESPACES('http://example.com/ns' AS ns), '/ns:feed/ns:entry' PASSING f.raw_xml COLUMNS entry_id text PATH 'ns:id', published date PATH 'ns:published', summary text PATH 'ns:summary') AS t;

→Namespaced Atom feed entries as typed rows

sql

INSERT INTO products (sku, name, price) SELECT t.sku, t.name, t.price FROM staging_xml, XMLTABLE('/catalog/product' PASSING staging_xml.payload COLUMNS sku text PATH '@sku', name text PATH 'name', price numeric PATH 'price' DEFAULT 0) AS t;

→Rows inserted from XML payload into a relational table

⚠Anti-Pattern— Parsing XML into rows in application code instead of using XMLTABLE

Pulling XML documents from PostgreSQL into application code and iterating over nodes to insert rows one by one is extremely slow: it requires one round-trip per row and prevents the database from using set-based INSERT optimisations.

✓ Instead: Use `INSERT INTO … SELECT … FROM XMLTABLE(…)` to shred the XML and write all rows in a single server-side statement.

Use `XMLTABLE` to shred XML responses from external APIs into rows directly in SQL, avoiding application-side XML parsing. The COLUMNS clause maps XPath expressions to typed columns.

example

SELECT t.* FROM xml_responses r, XMLTABLE('/response/item' PASSING r.xml_data COLUMNS id bigint PATH '@id', title text PATH 'title', price numeric PATH 'price' DEFAULT 0) AS t;

→XML response rows as a typed SQL result set

xpath

PG 8.3+→ xml[]

Evaluates an XPath expression against an XML value, returning an array of XML fragments matching the expression.

DeveloperData Eng

Signature

xpath ( xpath text, xml xml [, nsarray text[] ] ) → xml[]

Parameters

Parameter	Type	Description
xpath_expr	text	XPath expression
xml_doc	xml	XML document to query
nsarray	text[][]	Optional namespace prefix mappings, e.g., ARRAY[ARRAY['ns','http://example.com']]

Examples

sql

SELECT xpath('/foo/bar', '<foo><bar>42</bar><bar>88</bar></foo>'::xml);

→{<bar>42</bar>,<bar>88</bar>}

sql

SELECT xpath('//name/text()', doc) FROM xml_docs;

→All name text nodes

sql

SELECT unnest(xpath('/order/@id', doc)) FROM orders;

→Order ID attributes

sql

SELECT xpath('//ns:item/ns:price/text()', doc, ARRAY[ARRAY['ns','http://example.com/schema']]) FROM product_feeds;

→Price text nodes from a namespaced document

⚠Anti-Pattern— Using xpath() without namespace declarations on namespaced documents

When the source XML uses a default or explicit namespace, XPath expressions that omit the namespace prefix silently return empty arrays rather than raising an error. This is one of the most common causes of apparently working queries that return no data.

✓ Instead: Always supply the `nsarray` third argument when querying namespaced XML: `xpath('//ns:price/text()', doc, ARRAY[ARRAY['ns','http://example.com/ns']])`.

Use `xpath_exists(expr, doc)` to check if an element exists without extracting it. More efficient than checking `array_length(xpath(...), 1) > 0`.

example

SELECT * FROM orders WHERE xpath_exists('/order/discount', doc);

→Orders containing a discount element

xpath_exists

PG 8.3+→ boolean

Returns true if the XPath expression selects at least one node from the XML document.

DeveloperData Eng

Signature

xpath_exists ( xpath text, xml xml [, nsarray text[] ] ) → boolean

Parameters

Parameter	Type	Description
xpath_expr	text	XPath expression to evaluate
xml_doc	xml	XML document

Examples

sql

SELECT xpath_exists('/foo/bar', '<foo><bar>1</bar></foo>'::xml);

→true

sql

SELECT * FROM messages WHERE xpath_exists('//attachment', xml_body);

→Messages with attachments

sql

SELECT id FROM invoices WHERE xpath_exists('//lineitem[@type="discount"]', invoice_xml);

→Invoices containing at least one discount line item

sql

SELECT xpath_exists('//ns:record', doc, ARRAY[ARRAY['ns','http://example.com/v1']]) FROM feeds;

→true for documents containing a namespaced record element

⚠Anti-Pattern— Checking element existence via array_length(xpath(...), 1) instead of xpath_exists

Using `array_length(xpath('//element', doc), 1) > 0` allocates an XML array just to check existence. On large documents or high-volume tables this is measurably slower than `xpath_exists`, which short-circuits at the first match.

✓ Instead: Replace `array_length(xpath(expr, doc), 1) > 0` with `xpath_exists(expr, doc)` in WHERE clauses and CASE expressions.

`xpath_exists` is the most efficient way to test XML structure in a WHERE clause — it stops at the first match and doesn't build an array.

example

SELECT id FROM invoices WHERE xpath_exists('//lineitem[@type="discount"]', invoice_xml);

→Invoices containing discount line items

PostgreSQL XML Functions

Jump to function

What are PostgreSQL XML Functions?

xmlagg

Signature

Parameters

Examples

Common Gotchas

xmlconcat

Signature

Parameters

Examples

xmlelement

Signature

Parameters

Examples

xmlforest

Signature

Parameters

Examples

XMLTABLE

Signature

Parameters

Examples

xpath

Signature

Parameters

Examples

xpath_exists

Signature

Parameters

Examples

Related PostgreSQL Categories