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:

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

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’.

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:

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

The following output will appear after executing the above statements:

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

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.

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.

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.:

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:

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.

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.

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.

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.