PostgreSQL – Get One Row for Each Value in XML Column

postgresqlxml

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!

Best Answer

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

Related Solutions

Sql-server – Returning Multiple Result-Sets or Single XML Blods

I prefer multiple resultsets because its faster and uses less memory and much easier to deal with.

But everything depends on consumer of the data - for example if it can receive only XML - you have no choice 8-)

SQL Server 2012 XML – Combining Column Contents Without Loops

I used your @x from your previous question in my query below. I create a sample table with 2 rows that should behave just like your real table although my Name value is similar because I used @x twice (ID is not similar):

declare @table table(id int, value xml);
insert into @table(id, value) values(1, @x), (2, @x);

Select t.id
    , x.y.value('@title', 'varchar(100)') as name
    , null as col0
    , null as col1
    , null as col2
    , null as col3
    , null as col4
    , null as col5
    , null as col6
    , null as col7
From @table t
Cross Apply value.nodes('SizeGuide')x(y)
Union All
Select t.id
    , null
    , x.y.value('@Market', 'varchar(100)') as col0
    , x.y.value('HCol[1]/HValue[1]', 'varchar(100)') as col1
    , x.y.value('HCol[2]/HValue[1]', 'varchar(100)') as col2
    , x.y.value('HCol[3]/HValue[1]', 'varchar(100)') as col3
    , x.y.value('HCol[4]/HValue[1]', 'varchar(100)') as col4
    , x.y.value('HCol[5]/HValue[1]', 'varchar(100)') as col5
    , x.y.value('HCol[6]/HValue[1]', 'varchar(100)') as col6
    , x.y.value('HCol[7]/HValue[1]', 'varchar(100)') as col7
From @table t
Cross Apply value.nodes('SizeGuide/Headers/HConv/HCols')x(y)
Order By Id, Name desc, col0

Ouput:

id | name       | col0 | col1    | col2 | col3 | col4  | col5   | col6   | col7
1  | Baby - All | NULL | NULL    | NULL | NULL | NULL  | NULL   | NULL   | NULL
1  | NULL       | AT   | newborn | 0-3M | 3-6M | 6-12M | 12-18M | 18-24M | 2-3J
1  | NULL       | DE   | newborn | 0-3M | 3-6M | 6-12M | 12-18M | 18-24M | 2-3J
1  | NULL       | FR   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3a
1  | NULL       | UK   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3y
1  | NULL       | US   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3y
2  | Baby - All | NULL | NULL    | NULL | NULL | NULL  | NULL   | NULL   | NULL
2  | NULL       | AT   | newborn | 0-3M | 3-6M | 6-12M | 12-18M | 18-24M | 2-3J
2  | NULL       | DE   | newborn | 0-3M | 3-6M | 6-12M | 12-18M | 18-24M | 2-3J
2  | NULL       | FR   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3a
2  | NULL       | UK   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3y
2  | NULL       | US   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3y

I didnt used all columns and row from your xml in order to keep this shorter but you can easily add them.