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!

2 Answers 2


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
                         option_id integer path '.') as t

Online example


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

    (xpath('//text()', xt.option_id))[1] as id
    cross join unnest(xpath('/options/option_id', options_xml)) as xt(option_id);
  • You can simplify that if you use unnest(xpath('/options/option_id/text()', options_xml)) then you don't need the second xpath in the select list
    – user1822
    Feb 9, 2020 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.