Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL XML type.

The SQL standard includes a SQL/XML which introduces the predefined data type XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database, as per the Wikipedia page.

Table of Contents

The XML Data Type

PostgreSQL implements the XML data type, which is documented in the chapters on XML type and XML functions chapters.

The best option when you need to process XML documents might be the XSLT transformation language for XML. It should be no surprise that a PostgreSQL extension allows writing stored procedures in this language. If you have to deal with XML documents in your database, check out PL/XSLT.

Processing XML with PL/XSLT

An example of a PL/XSLT function follows:

create extension plxslt;

CREATE OR REPLACE FUNCTION striptags(xml) RETURNS text
	LANGUAGE xslt
AS $$<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns="http://www.w3.org/1999/xhtml"
>

  <xsl:output method="text" omit-xml-declaration="yes"/>

  <xsl:template match="/">
    <xsl:apply-templates/>
  </xsl:template>

</xsl:stylesheet>
$$;

It can be used like this:

create table docs
 (
   id      serial primary key,
   content xml
 );

insert into docs(content)
     values ('<?xml version="1.0"?>
<html xmlns="http://www.w3.org/1999/xhtml">
<body>hello</body>
</html>');

select id, striptags(content)
  from docs;

As expected, here’s the result:

 id │ striptags 
════╪═══════════
  1 │          ↵
    │ hello    ↵
    │ 
(1 row)

Conclusion

The XML support in PostgreSQL might be handy in cases. It’s mainly been added for standard compliance, though, and is not found a lot in the field. XML processing function and XML indexing is pretty limited in PostgreSQL.

This article is an extract from my book The Art of PostgresQL, which teaches SQL to developers so that they may replace thousands of lines of code with very simple queries. The book has a full chapter about data types in PostgreSQL, check it out!