Supported Versions: Current (16) / 15 / 14 / 13 / 12
Development Versions: devel
Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

8.14. XML Document Support

XML (Extensible Markup Language) support is not one capability, but a variety of features supported by a database system. These capabilities include storage, import/export, validation, indexing, efficiency of modification, searching, transforming, and XML to SQL mapping. PostgreSQL supports some but not all of these XML capabilities. Future releases of PostgreSQL will continue to improve XML support. For an overview of XML use in databases, see http://www.rpbourret.com/xml/XMLAndDatabases.htm.

Storage

PostgreSQL does not have a specialized XML data type. Users should store XML documents in ordinary TEXT fields. If you need the document split apart into its component parts so each element is stored separately, you must use a middle-ware solution to do that, but once done, the data becomes relational and has to be processed accordingly.

Import/Export

There is no facility for mapping XML to relational tables. An external tool must be used for this. One simple way to export XML is to use psql in HTML mode (\pset format html), and convert the XHTML output to XML using an external tool.

Validation

/contrib/xml2 has a function called xml_is_well_formed() that can be used in a CHECK constraint to enforce that a field contains well-formed XML. It does not support validation against a specific XML schema. A server-side language with XML capabilities could be used to do schema-specific XML checks.

Indexing

/contrib/xml2 functions can be used in expression indexes to index specific XML fields. To index the full contents of XML documents, the full-text indexing tool /contrib/tsearch2 can be used. Of course, Tsearch2 indexes have no XML awareness so additional /contrib/xml2 checks should be added to queries.

Modification

If an UPDATE does not modify an XML field, the XML data is shared between the old and new rows. However, if the UPDATE modifies an XML field, a full modified copy of the XML field must be created internally.

Searching

XPath searches are implemented using /contrib/xml2. It processes XML text documents and returns results based on the requested query.

Transforming

/contrib/xml2 supports XSLT (Extensible Stylesheet Language Transformation).

XML to SQL Mapping

This involves converting XML data to and from relational structures. PostgreSQL has no internal support for such mapping, and relies on external tools to do such conversions.

Missing Features

Missing features include XQuery, SQL/XML syntax (ISO/IEC 9075-14), and an XML data type optimized for XML storage.