CODEWITHSUNDEEP
xmlpostgresql
Nigel
Nigel

Reputation: 1027

Extracting unique attributes of xml elements with postgresql

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!

Upvotes: 0

Views: 463

Answers (1)

Jeremy
Jeremy

Reputation: 6723

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;

Upvotes: 1

Related Questions