This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on the XML data type of PostgreSQL.
Explain with the sample example.
What is XML Type ?
The XML Data type is used to store XML data. XML is well-formed storage structure of data. You can store multiple nodes into a single XML column. You can store data in hierarchical format.
E.g,
User preferences table, in which User can select multiple preferences and even User can create custom choices for them.
Now, how you can store this preferences data for those Users.
Simple, create one XML column and store all User preferences id into this. like,
<preferences><id>1</id><id>2</id><id>4</id></preferences>.
Below is a demonstration of XML with PostgreSQL.
Basically you can store XML data with one child node or with different child node.
Les’s First start with one child node:
Create table with XML type:
|
1 |
CREATE TABLE XMLTEST (Name XML); |
Now Insert XML Data.
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO XMLTEST VALUES (' abc xyz pqr cdf '); |
Select data from XMLTEST:
|
1 |
SELECT *FROM XMLTEST |
You can find above full XML into one column.
Now, time to parse this XML column using unnest() function of PostgreSQL.
|
1 |
SELECT unnest(xpath('.//Sub/text()', Name::xml))::text AS XMLDATA FROM XMLTEST; |
Result is:
You can see this is four rows now, as we have stored different four values into one XML Column and after parse, we can see those four values in the form of different row.
Demonstration of different child node:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
INSERT INTO XMLTEST VALUES (' '); SELECT unnest(xpath('//Name/text()', Name::XML)) AS Name ,unnest(xpath('//City/text()', Name::XML)) AS City ,unnest(xpath('//Gender/text()', Name::XML)) AS Gender ,unnest(xpath('//Age/text()', Name::XML)) AS Age FROM XMLTEST; |
Result is :
In the above code, we inserted an XML data with different child nodes.
You can see the result of one row with the various columns for the different child nodes.
-
September 16, 2015 at 5:02 pm
Thanks Anvesh,
good article !
Really You solved my problem for postgres XML. -
May 5, 2016 at 5:45 am
can you give an example of a query that involve where clause in the xpath ?
-
May 13, 2016 at 9:21 am;WITH cteXML
AS
(
SELECT
CAST(unnest(xpath(‘//Name/text()’, Name::XML)) AS TEXT) AS Name
,CAST(unnest(xpath(‘//City/text()’, Name::XML)) AS TEXT) AS City
,CAST(unnest(xpath(‘//Gender/text()’, Name::XML)) AS TEXT) AS Gender
,CAST(unnest(xpath(‘//Age/text()’, Name::XML)) AS TEXT) AS Age
FROM XMLTEST
)
SELECT *FROM cteXML
WHERE Name=’Anvesh’;
-
-
March 16, 2018 at 1:07 pm
can u please give the simple example of update and delete querry for the same
-
February 17, 2020 at 12:10 pm
Hi Anvesh,
Am getting this Error ,
DETAIL: This functionality requires the server to be built with libxml support.
HINT: You need to rebuild PostgreSQL using –with-libxml.How to resolve.
