Preamble
XML is a very popular data type today. There are many databases that support saving XML data types ((IBM, DB2, Oracle, SQL Server). PostgresSql is also one of them. This article will dive into how to save postgresSql data type. as well as common functions in PostgresSql.
Insert XML Data
When creating a column in a table and formatting the type as xml, PostgesSql understands to use the xml type by default. PostgresSql will automatically validate the data type when inserting the correct XML type.
For example :
Create a table using columns of type XML
CREATE TABLE families (id serial PRIMARY KEY, profile xml);
Perform insert data into XML
1 2 3 4 5 6 7 8 9 | INSERT INTO families(profile) VALUES ( '<family name="a"> <member><relation>father</relation><name>David</name></member> <member><relation>mother</relation><name>Sonia</name></member> <member><relation>son</relation><name>Brandon</name></member> <member><relation>daughter</relation><name>Azaleah</name></member> </family>'); |
Note: The data type in the profile column is currently XML, so it must insert correctly with the syntax of XML.
Result:
1 2 3 4 | INSERT 0 1 Query returned successfully in 55 msec. |
In addition, we can create an XML table and insert data as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | CREATE TABLE hoteldata AS SELECT xml $$<hotels> <hotel id="mancha"> <name>La Mancha</name> <rooms> <room id="201"><capacity>3</capacity><comment>Great view of the Channel</comment></room> <room id="202"><capacity>5</capacity></room> </rooms> <personnel> <person id="1025"> <name>Ferdinando Quijana</name><salary currency="PTA">45000</salary> </person> </personnel> </hotel> <hotel id="valpo"> <name>Valparaíso</name> <rooms> <room id="201"><capacity>2</capacity><comment>Very noisy</comment></room> <room id="202"><capacity>2</capacity></room> </rooms> <personnel> <person id="1026"><name>Katharina Wuntz</name><salary currency="EUR">50000</salary></person> <person id="1027"><name>Diego Velázquez</name><salary currency="CLP">1200000</salary></person> </personnel> </hotel> </hotels>$$ AS hotels; |
The above statement will create a table named hoteldata with the column name hotels with XML type
Query DATA XML
To perform data queries with type XML, we cannot use the normal way of querying data.
For example :
1 2 | Select * from families; |
Result : 
Looking at the above result, PostgresSql is returning the result in XML format and it is very difficult for us to process such result.
To perform XML queries, PostgresSql supports two basic functions: XPath and XMLTABLE
Queries are based on XPath
Syntax:
xpath(xpath, xml [, nsarray])
The first argument is the xpath query and the second is an XML object. The output is an array of XML elements that satisfy the XPath query.
For example :
1 2 3 4 5 6 7 8 9 10 | SELECT ordinality AS id, family, (xpath('/member/relation/text()', f))[1]::text As relation, (xpath('/member/name/text()', f))[1]::text As mem_name FROM ( SELECT (xpath('/family/@name', profile))[1]::text As family, f.ordinality, f.f FROM families, unnest(xpath('/family/member', profile)) WITH ORDINALITY AS f ) x; |
Result:
Explain the statement
- The first 2 xpath statements retrieve the text values of the name and relation element in each member element of XML. Because XPath returns an array, we have to use sub-array
[1]::textto get the value we need. - The next XPath statement gets the attribute name from the family root. We use @ property_name.
The query is based on XMLTable
Since PostgresSql10, we can use the XMLTable function to query XML values.
Syntax:
1 2 3 4 5 6 7 | xmltable( [XMLNAMESPACES(namespace uri AS namespace name[, ...]), ] row_expression PASSING [BY REF] document_expression [BY REF] COLUMNS name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL] | FOR ORDINALITY } [, ...] ) |
For example :
1 2 3 4 5 6 7 8 9 10 | SELECT xt.* FROM families, XMLTABLE ('/family/member' PASSING profile COLUMNS id FOR ORDINALITY , family text PATH '../@name' , relation text NOT NULL , member_name text PATH 'name' NOT NULL ) AS xt; |
Result:
Looking at the above statement, you can see that the use of XMLTable is more concise. Explain the above example:
The first part is an XML path that identifies the row. The word PASSING indicates the column to be analyzed. This column must be of type XML. Here is the profile column The Column keyword is used to define the list of columns to be parsed. FOR ORDINALITY is used in the id field to assign an automatic serial number to each record that is returned
They can use ../ to move up one level of the standing row. In this case, we use ./ @name to get the family name.
Some common functions for handling XML in PostgresSql
PostgresSql provides many functions to handle XML in PostgresSql. Here are some common functions
xmlcomment ()
This is a function used to create comments in XML. Similar to comments in XML, ‘-‘ and ‘-‘ are added before and after the value to turn it into a valid XML comment. If the argument is null, the result is null.
Syntax: xmlcomment(text)
For example:
1 2 | SELECT xmlcomment('Comment XML'); |
Result: 
xmlconcat ()
A list of XML values are joined together to create an XML value using this function.
Syntax: xmlconcat(xml[, ...])
For example: SELECT xmlconcat('<books/>', '<book>tutorials</book>');
Result :
xmlelement ()
This function is used to create elements for XML based on name, attribute and content
Syntax: xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...])
For example: SELECT xmlelement(name books, xmlattributes('SQL' as title));
Result: 
Postgres functions can be used in the xmlelement () function as follows: For example: SELECT xmlelement(name calendar, xmlattributes(current_date as date), 'current', ' date');
Result: 
xmlforest ()
The xmlforest function creates a sequence of XML elements using the given name and content.
Syntax: xmlforest(content [AS name] [, ...])
For example: SELECT xmlforest('book' AS newelement, 123 AS number);
Result : 
xmlroot ()
The xmlroot function is used to change the root node properties of an XML value.
Syntax: xmlroot(xml, version text | no value [, standalone yes|no|no value])
For example :
1 2 3 | SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>tutorials</content>'), version '1.0', standalone yes); |
Result : 
Conclude
Through the above article, hope everyone will know how to use XML stored in PostgresSql.