PostgreSQL is well known for its rich extensibility. One such extension is xml2, a legacy but still useful extension that provides helper functions to process, validate, query, and transform XML data stored as plain text.
Although PostgreSQL offers a native XML data type, the xml2 extension continues to exist for backward compatibility, legacy applications, and simple XML processing needs.
In this blog, we will walk step by step through:
- What the xml2 extension is
- Why it exists and when it is useful
- How to enable it
- A detailed explanation of each function, using real examples executed directly in PostgreSQL
All examples shown here are tested and runnable.
What is the xml2 Extension?
The xml2 extension provides a collection of XPath and XSLT-related utility functions that work on XML stored as TEXT.
Key characteristics
- Works on TEXT, not the native xml type
- Supports XPath queries
- Supports XSLT transformations
- Mostly used in older PostgreSQL systems
- Maintained mainly for compatibility reasons
Creating the xml2 Extension
Before enabling the extension, check that the extension is already installed
select * from pg_available_extensions where name = 'xml2';
If it is already installed,you get a result like this
name | default_version | installed_version | comment
------+-----------------+-------------------+-------------------------
xml2 | 1.1 | 1.1 | XPath querying and XSLT
(1 row)
If it is not installed, then create the extension like this
CREATE EXTENSION xml2;
Verify installation:
\dx+ xml2
This lists all functions provided by the extension.
You get a result like this
Objects in extension "xml2"
Object description
------------------------------------------------
function xml_encode_special_chars(text)
function xml_valid(text)
function xpath_bool(text,text)
function xpath_list(text,text)
function xpath_list(text,text,text)
function xpath_nodeset(text,text)
function xpath_nodeset(text,text,text)
function xpath_nodeset(text,text,text,text)
function xpath_number(text,text)
function xpath_string(text,text)
function xpath_table(text,text,text,text,text)
function xslt_process(text,text)
function xslt_process(text,text,text)
XML Utility Functions
1. xml_encode_special_chars(text)
This function converts special characters that are not allowed directly in XML into their corresponding XML-safe escape sequences. Characters like <, >, and & can break XML parsing if they appear as raw text. This function ensures that such text can be safely embedded inside an XML document without causing syntax errors.
This is particularly useful when user input or dynamic strings need to be stored or transmitted as XML.
SELECT xml_encode_special_chars('5 < 10 & 10 > 5');Output:
xml_encode_special_chars
---------------------------
5 < 10 & 10 > 5
(1 row)
2. xml_valid(text)
This function checks whether the provided text is a well-formed XML document. It does not validate against an XML schema; instead, it verifies basic XML structure rules such as proper opening and closing tags.
One important behavior to understand is that plain text without XML tags is also considered valid XML.
SELECT xml_valid('<user><name>Marc demo</name></user>');Returns true for valid XML and false when the structure is broken.
Output :
xml_valid
-----------
t
(1 row)
XPath Functions
3. xpath_bool(xml, xpath)
This function evaluates an XPath expression as a boolean condition. It is typically used to check whether certain conditions inside an XML document are satisfied.
It is especially useful when XML contains configuration values, flags, or numeric thresholds that need to be validated directly inside SQL queries.
SELECT xpath_bool(
'<user><age>21</age></user>',
'/user/age > 18'
);
The function returns true or false based on the XPath condition.
Output :
xpath_bool
------------
t
(1 row)
4. xpath_list(xml, xpath)
This function extracts multiple matching XML nodes and returns their text values as a comma-separated string. It is useful when an XML document contains repeated elements and you want to flatten them into a single SQL value.
This is commonly used in reporting or logging scenarios where XML data needs to be summarized.
SELECT xpath_list(
'<skills><skill>Python</skill><skill>C</skill></skills>',
'/skills/skill'
);
You get a result like this
xpath_list
------------
Python,C
(1 row)
5. xpath_list(xml, xpath, separator)
This variation of xpath_list allows you to control how the extracted values are joined together by providing a custom separator. This makes the output more readable and suitable for display purposes.
It is useful when XML values are shown directly to users or exported into reports.
SELECT xpath_list(
'<skills><skill>Python</skill><skill>C</skill></skills>',
'/skills/skill',
' | '
);
Output :
xpath_list
------------
Python | C
(1 row)
6. xpath_nodeset(xml, xpath)
This function returns the actual XML nodes that match the XPath expression, not just their text values. The result is still returned as text, but it preserves the XML structure.
This is useful when you need to extract and reprocess parts of an XML document or pass XML fragments to another system.
SELECT xpath_nodeset(
'<users><user><id>1</id></user></users>',
'/users/user'
);
Output :
xpath_nodeset
-------------------------
<user><id>1</id></user>
(1 row)
7. xpath_number(xml, xpath)
This function extracts a numeric value from an XML document using XPath and automatically converts it into a PostgreSQL numeric type.
It is useful when XML stores numeric configuration values, prices, or counters that need to be compared or calculated in SQL.
SELECT xpath_number(
'<order><price>150</price></order>',
'/order/price'
);
Output :
xpath_number
--------------
150
(1 row)
8. xpath_string(xml, xpath)
This function extracts the string value of the first XML node that matches the XPath expression. It is one of the most commonly used functions in the xml2 extension.
It is ideal for fetching single values like names, identifiers, or status fields from XML documents.
SELECT xpath_string(
'<user><name>Marc demo</name></user>',
'/user/name'
);
Output :
xpath_string
--------------
Marc demo
(1 row)
9. xpath_table(...)
Purpose:
xpath_table() is one of the most powerful yet complex functions provided by the xml2 extension.
It converts XML data stored in a table column into a relational table format, allowing XML documents to be queried using standard SQL.
This function was commonly used before PostgreSQL introduced modern XML and JSON processing features. Today, it is primarily encountered in legacy systems due to its complexity and confusing interface.
What it does
- Reads XML from a table column
- Splits repeating XML nodes into rows
- Extracts values using XPath expressions
- Returns the result as a relational table
CREATE TABLE xml_store (
users TEXT,
doc XML
);
INSERT INTO xml_store VALUES (
'row1',
'<users>
<user><id>1</id><name>A</name></user>
<user><id>2</id><name>B</name></user>
</users>'
);
SELECT *
FROM xpath_table(
'users', -- key column
'doc', -- XML column
'xml_store', -- table name
'/users/user/id | /users/user/name',
'true' -- WHERE condition
) AS t(
users TEXT,
id TEXT,
name TEXT
);
You get result like this
users | id | name
-------+----+------
row1 | 1 | A
row1 | 2 | B
(2 rows)
XSLT Functions
XSLT functions are functions used in XSLT (Extensible Stylesheet Language Transformations), which is a language designed to transform XML documents into other formats.
XSLT is a rule-based language used to:
- Transform XML - XML
- Transform XML - HTML
- Transform XML - text (CSV, plain text, etc.)
It works by applying templates and functions to XML data.
10. xslt_process(xml, xslt)
Purpose:
Applies an XSLT transformation to XML.
SELECT xslt_process(
'<user><name>Marc demo</name></user>',
'<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<output>
<xsl:value-of select="/user/name"/>
</output>
</xsl:template>
</xsl:stylesheet>'
);
You get result like this
xslt_process
------------------------
<?xml version="1.0"?> +
<output>Marc demo</output>+
(1 row)
When Should You Use xml2?
Use xml2 when:
- Maintaining legacy PostgreSQL applications
- Working with XML stored as plain text
- Performing simple XPath or XSLT operations
Avoid it for:
- New application designs
- High-performance XML workloads
- Schema-validated XML processing
For modern use cases, PostgreSQL's native xml type or jsonb is recommended.
Conclusion
The xml2 extension is an older part of PostgreSQL, but it is still useful for learning and for certain real-world cases. It comes from a time when XML was commonly used to exchange structured data, before features like jsonb became popular and stable. The extension provides tools for XML checks, XPath queries, and XSLT processing.
Today, most PostgreSQL projects prefer jsonb for speed and flexibility, or the built-in xml data type for strict XML rules. Even so, xml2 can still be helpful in some situations. Knowing how it works is useful when:
- Working with or upgrading older PostgreSQL databases
- Reviewing old database designs that store XML as plain text
- Learning how PostgreSQL extensions are built and connected to the core system