How to use xpath in Postgres to align XML elements

Question

This query:

with x as (select
'<promotions>
    <promotion promotion-id="old-promotion">
        <enabled-flag>true</enabled-flag>
        <searchable-flag>false</searchable-flag>
    </promotion>
    <promotion promotion-id="new-promotion">
        <enabled-flag>false</enabled-flag>
        <searchable-flag>false</searchable-flag>
        <exclusivity>no</exclusivity>
        <price>100</price>
        <price>200</price>
        <price>300</price>
    </promotion>
</promotions>'::xml as t
)
select 
    xpath('/promotions/promotion/@promotion-id', t) promotion_id,
    xpath('/promotions/promotion/enabled-flag/text()', t) enabled_flag,
    xpath('/promotions/promotion/exclusivity/text()', t) exclusivity,
    xpath('/promotions/promotion/price/text()', t) price
from x

for which the elements exclusivity and price do not appear in the first promotion, but do appear in the second, yields this result:

"{old-promotion,new-promotion}";"{true,false}";"{no}";"{100,200,300}"

I need to figure out how I can get a result set which will allow me to line up the exclusivity and price data to their appropriate promotion_id's. Something like this

"{old-promotion,new-promotion}";"{true,false}";"{,no}";"{,"100,200,300"}"

What do I have to do to make that happen?

score 4 · Accepted Answer · edited May 23 '17 at 12:25

Consider this:

WITH x AS (SELECT
'<promotions>
    <promotion promotion-id="old-promotion">
        <enabled-flag>true</enabled-flag>
        <searchable-flag>false</searchable-flag>
    </promotion>
    <promotion promotion-id="new-promotion">
        <enabled-flag>false</enabled-flag>
        <searchable-flag>false</searchable-flag>
        <exclusivity>no</exclusivity>
        <price>100</price>
        <price>200</price>
        <price>300</price>
    </promotion>
</promotions>'::xml AS t
)
SELECT xpath('//@promotion-id',       node) promotion_id
      ,xpath('//enabled-flag/text()', node) enabled_flag
      ,xpath('//exclusivity/text()',  node) exclusivity
      ,xpath('//price/text()',        node) price
FROM (SELECT unnest(xpath('/promotions/promotion', t)) AS node FROM x) sub

The trick is to split the document into nodes first with the help of unnest().
Then extract attributes from each node. This way you get NULL values automatically for missing attributes.

Returns:

  promotion_id   | enabled_flag | exclusivity |     price
-----------------+--------------+-------------+---------------
 {old-promotion} | {true}       | {}          | {}
 {new-promotion} | {false}      | {no}        | {100,200,300}

Related question:
XPath query into hierarchical data, preserving ancestor–descendant relationship

How to use xpath in Postgres to align XML elements

1 Answers1