Get one row for each value in xml column

Question

I have a table, called product_variants, with one integer column and one xml column containing a set of foreign key values.

If I ran,

select product_id, options_xml from product_variants

This would be a typical result

product_id  |  options_xml
132         |  <options><option_id>1</option_id><option_id>8</option_id></options>

I would like to somehow select a new row for each option_id.

The result I want,

product_id  |  option_id
132         |  1
132         |  8

Thank you in advance!

Answer 1

you can use xmltable() for that:

select p.product_id, t.option_id
from product_variants p
  cross join xmltable('/options/option_id'
                      passing p.options_xml
                      columns 
                         option_id integer path '.') as t

Online example

Answer 2

I prefer a_horse_with_no_names answer but thought I would share the other solution I found.

SELECT
    product_variants.options_xml,
    (xpath('//text()', xt.option_id))[1] as id
FROM
    product_variants
    cross join unnest(xpath('/options/option_id', options_xml)) as xt(option_id);