Get one row for each value in xml column 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         |  &lt;options&gt;&lt;option_id&gt;1&lt;/option_id&gt;&lt;option_id&gt;8&lt;/option_id&gt;&lt;/options&gt;


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!
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