0

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!

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

0

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);
1
  • 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 Feb 9 '20 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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