PostgreSQL, using xml

Question

Here is example table used for getting some basic operations with xml column in postgreSQL table.

DROP TABLE IF EXISTS temp1;
CREATE TABLE temp1(myindex serial PRIMARY KEY, description xml);
INSERT INTO  temp1(description)
VALUES
('<?xml version="1.0" encoding="utf-8"?>
<setup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DATABASE>herdatabase</DATABASE>
  <DBSERVER>127.0.0.1</DBSERVER>
  <DBUSER>saly</DBUSER>
  <DBPORT>5432</DBPORT>
</setup>'),

('<?xml version="1.0" encoding="utf-8"?>
<setup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DATABASE>mydatabase</DATABASE>
  <DBSERVER>127.0.0.1</DBSERVER>
  <DBUSER>john</DBUSER>
  <DBPORT>4424</DBPORT>
</setup>');

I decided to use XML instead of hstore and JSON since I'm working in .NET where XML functions and serialization is well supported and I haven't much of such data so speed is not much important.

From here I try some basic queries to get data.

--1) That one work
SELECT xpath('/setup/DBPORT/text()', description) FROM temp1;

--2) That work but give two arrays with single value
--   How to get one array with 2 values like "{5432, 127.0.0.1}"
SELECT xpath('/setup/DBPORT/text()', description), xpath('/setup/DBSERVER/text()', description) FROM temp1;

--3) How to get description when condition is met?
--   Here I get ERROR: could not identify an equality operator for type xml 
SELECT description FROM temp1 WHERE xpath('/setup/DBSERVER/text()', description) = '{127.0.0.1}';

--4) How to get all values when condition is met?
SELECT allvalues FROM temp1 WHERE xpath('/setup/DBUSER/text()', description) = 'john';

How to get working those queries which don't work?

score 3 · Accepted Answer · 2014-01-28 17:21:54Z

3

2 - Use the XPath "or" operator, |, to select either a DBPORT or DBSERVER:

SELECT xpath('/setup/DBPORT/text()|/setup/DBSERVER/text()', description)
FROM temp1;

3 - The xpath() function returns an XML array which can be cast to a TEXT array for easier matching to other values:

SELECT description
FROM temp1
WHERE xpath('/setup/DBSERVER/text()', description)::TEXT[] = '{127.0.0.1}'::TEXT[];

4 - Similar to the previous, cast the XML array to a Text array to match to a value:

SELECT xpath('/setup/node()/text()', description)
FROM temp1
WHERE xpath('/setup/DBUSER/text()', description)::TEXT[] = '{john}'::TEXT[];

edited Jan 28, 2014 at 17:21

answered Jan 28, 2014 at 16:38

user1322772

Sign up to request clarification or add additional context in comments.

2 Comments

Wine Too Over a year ago

Hi gareth, thanks for answer. Under allvalues I though like this '{mydatabase, 127.0.0.1, john, 4424}', not 'description' as text.

user1322772 Over a year ago

You can use XPath to query out the values you need as an array - I've updated my original answer with the new query for point 4.

yieldsfalsehood · Accepted Answer · 2014-01-28 16:38:35Z

2

For the second, you have two arrays, so you can use array_cat():

SELECT array_cat(xpath('/setup/DBPORT/text()', description),
                 xpath('/setup/DBSERVER/text()', description))
FROM temp1;

For the third, you have one array of values (it's possible that your xpath matches multiple /setup/DBSERVER elements, thus the array type). This takes the first element from the array and casts to text so that you can compare to the string

SELECT description
FROM temp1
WHERE (xpath('/setup/DBSERVER/text()', description))[1]::text = '127.0.0.7';

Finally, you can use an xpath to generate an array of your elements, then unnest() them (so you get one row per element), then use another xpath to get at the element content. This gives the element content, but not the element name - I don't know the xpath to get the tag name off the top of my head.

SELECT xpath('/', unnest(xpath('/setup/*', description)))
FROM temp1
WHERE (xpath('/setup/DBUSER/text()', description))[1]::text = 'john';

answered Jan 28, 2014 at 16:38

yieldsfalsehood

3,1051 gold badge21 silver badges15 bronze badges

1 Comment

Wine Too Over a year ago

Can I get all values like in last case (unnnest) as result in one row but 4 columns or in one row and one column written as array like '{mydatabase, 127.0.0.1, john, 4424}? Query in the middle seem's to not work.

Collectives™ on Stack Overflow

PostgreSQL, using xml

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related