PostgreSQL

PostgreSQL XML Data Type

The full form of XML is eXtensible Markup Language. Each element or tag of the XML is defined by the user. The information can be transmitted easily from one location to another by using XML data. The XML data are stored in a hierarchical format. PostgreSQL database supports XML data type to store XML data. The way to use XML data type in the PostgreSQL table has been shown in this tutorial.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-GET -y install postgresql postgresql-contrib

$ sudo systemctl START postgresql.service

Run the following command to login to PostgreSQL with root permission:

$ sudo -u postgres psql

Use of PostgreSQL data type:

Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’.

# CREATE DATABASE testdb;

The following output will appear after creating the database:

A. XML data with one child node

Run the following CREATE query to create a table named xmldoc1 with a field of XML data type:

# CREATE TABLE xmldoc1 (xmldata XML);

Run the following INSERT query to add XML data with one child node in the xmldata field of XML data type:

# INSERT INTO xmldoc1

VALUES (

'

          <a href="mailto:[email protected]">[email protected]</a>

          <a href="mailto:[email protected]">[email protected]</a>

          Site is down

          My site is not working.

'
);

The following output will appear after executing the above statements:

Run the following SELECT query to read all records from the xmldoc1 table:

# SELECT * FROM xmldoc1;

The following output will appear after executing the above statement:

B. XML data with different child nodes

Run the following CREATE query to create a table named xmldoc2 with two fields. The first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is xmldata and the data type is XML.

# CREATE TABLE xmldoc2 (
id SERIAL PRIMARY KEY,
      xmldata XML );

The following output will appear if the table is created successfully:

Run the following INSERT query to insert XML data of different child node. Here, the XML data with four child nodes will be inserted in the xmldata field.

# INSERT INTO xmldoc2 (xmldata)

        VALUES ('<?xml version="1.0"?>

        <Email>

                <to>[email protected]</to>

                <from>[email protected]</from>

                <subject>Site is down</subject>

                <message>My site is not working.</message>

        </Email>'
);

The following output will appear if the INSERT query is executed successfully:

Run the following SELECT query to read the value of each node of the XML document in each field separately.:

# SELECT unnest(xpath('//to/text()', xmldata::XML)) AS TO,

        unnest(xpath('//from/text()', xmldata::XML)) AS FROM,

        unnest(xpath('//subject/text()', xmldata::XML)) AS Subject,

        unnest(xpath('//message/text()', xmldata::XML)) AS Message

FROM xmldoc2;

C. Convert XML data into a table

Run the following CREATE query to create a table named booklist that will convert the XML data into a table:

# CREATE TABLE booklist AS SELECT xml

   $$<books>

      <book id="01">

         <title>XML Developers Guide</title>

         <author>Gambardella AND Matthew</author>

         <price>44.95</price>

      </book>

      <book id="02">

      <title>Midnight Rain</title>

         <author>Ralls AND Kim</author>

         <price>5.95</price>

      </book>

      <book id="03">

         <title>Maeve Ascendant</title>

         <author>Corets AND Eva</author>

         <price>5.95</price>

      </book>

</books>$$ AS books;

The following output will appear if the XML data is converted into a table properly:

The content of the XML nodes can be retrieved by defining the path of the node properly. The xmltable.* is one of the ways to read the node and attribute values from the table that was converted from the XML data. Run the following SELECT query to read the value of the id attribute of the book node and the values of the title, author, and price nodes. Here, the ‘@’ symbol has used to read the attribute value.

# SELECT xmltable.*  FROM booklist,

   XMLTABLE ('/books/book' PASSING books

      COLUMNS

         id CHAR(2) PATH '@id' NOT NULL,

         title TEXT PATH 'title' NOT NULL,

         author TEXT PATH 'author' NOT NULL,

         price FLOAT PATH 'price' NOT NULL );

The following output will appear after executing the above query:

The values of the XML nodes and attributes can be retrieved from the table by defining the field names in the SELECT query based on the XML nodes. Run the following SELECT query to read the value of the id attribute of the book node and the values of the title, author, and price nodes. Here, the ‘@’ symbol has been used to read the attribute value like the previous SELECT query.

# SELECT Id, Title, Author, Price FROM booklist,

   XMLTABLE ('/books/book' PASSING books

      COLUMNS

         Id INT PATH '@id' NOT NULL,

         Title VARCHAR (50) PATH 'title' NOT NULL,

         Author VARCHAR (30) PATH 'author' NOT NULL,

         Price FLOAT PATH 'price' NOT NULL);

The following output will appear after executing the above query:

The aggregate function can be applied to the node of the XML by using the SELECT query. Run the following SELECT query to count the total number of id attribute and total price of all books by using the COUNT() function and SUM() function. According to the XML data, the total number of id attributes is 3 and the sum of all price nodes is 56.85.

# SELECT COUNT(id) AS Total_Books, SUM(Price) AS Total_Price FROM booklist,

   XMLTABLE ('/books/book' PASSING books

      COLUMNS

         id INT PATH '@id' NOT NULL,

         tittle VARCHAR (50) PATH 'title' NOT NULL,

         author VARCHAR (30) PATH 'author' NOT NULL,

         price FLOAT PATH 'price' NOT NULL);

The following output will appear after executing the above query:

Conclusion:

The way of using XML data type in PostgreSQL tables and reading the XML data from the table in different ways have been shown in this tutorial for helping the new PostgreSQL users to know the uses of this data type properly.

About the author

Fahmida Yesmin

I am a trainer of web programming courses. I like to write article or tutorial on various IT topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.