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?

Community · Accepted Answer · 2017-05-23 12:25:43Z

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

Collectives™ on Stack Overflow

How to use xpath in Postgres to align XML elements

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related