Extracting unique attributes of xml elements with postgresql

Question

Given this xml data type in a postgresql database:

<parent>
  <boy age="12">Joe</child>
  <boy age="13">John</child>
  <boy age="12">Jimmy</child>
  <girl age="13">John</child>
  <girl age="11">Jimmy</child>

</parent>

How to extract the unique attributes of all the child elements?

This is what I've done so far: SELECT unnest(xpath('(//boy|girl)/@age', col_name)) from table;

It returns this:

unnest
12
13
12
13
11

I'd like to return only 11,12,13. So, my next step is to try DISTINCT. I've scoured the internet and tried a few things but it doesnt seem to work:

Method 1 SELECT DISTINCT unnest(xpath('(//boy|girl)/@age', col_name)) from table; I get this error: could not identify an equality operator for type xml

Method 2 - using subquery - I thought this should be the answer. I run a query which returns a table of all the attributes. Then I run a select distinct on that table like this:
SELECT DISTINCT * from (SELECT unnest(xpath('(//a|b|c)/@z', data)) from test3) as x;

I still get the same error of could not identify an equality operator for type xml

I've also tried this:
SELECT DISTINCT x from (SELECT unnest(xpath('(//a|b|c)/@z', data)) from test3) as x;

I get could not identify a comparison function for type xml.

Appreciate any help!

Jeremy · Accepted Answer · 2019-10-15 12:06:11Z

1

You can cast the result of your xpath to text, or text and then to int:

SELECT DISTINCT unnest(xpath('(//boy|girl)/@age', col_name))::text::int from table;

edited Oct 15, 2019 at 12:06

answered Oct 14, 2019 at 17:11

Jeremy

6,77322 silver badges21 bronze badges

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

2 Comments

Nigel Over a year ago

Perfect. That works! Whats the reason for me casting the result of xpath to text or text to int for DISTINCT to work? Btw, it requires ::for text as well :-)

Jeremy Over a year ago

Good catch on the :: I fixed that in my post. The result of the xpath function is of type xml and there's no built-in method to convert from xml to int. However, there is a cast from xml to text and a cast from text to int.

Collectives™ on Stack Overflow

Extracting unique attributes of xml elements with postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related