Parsing XML document in PostgreSQL using XPATH?

Question

Is there a way to parse the following XML document in PostgreSQL using XPATH to obtain desired output as

--Corrected required output

promotion-id price new-promotion null new-promotion null new-promotion 300

for the price element. Currently when I run the query provided below I get 300 as the output. My issue is my query ignores the price elements having <price xsi:nil="true"/> structure.

Is there a way to return null as result for the price elements with <price xsi:nil="true"/> type structure?

My code is something like:

WITH x AS (SELECT
'<promotions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <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 xsi:nil="true"/>
        <price xsi:nil="true"/>
        <price>300</price>
    </promotion>
</promotions>'::xml AS t
)
SELECT unnest(xpath('//price/text()', node))::text 
FROM (SELECT unnest(xpath('/promotions/promotion', t)) AS node FROM x) sub

Any suggestions for the above issue is highly appreciated.

gwaigh · Answer 1 · 2017-09-11T16:39:02.103

The problem is that there is no text to be extracted from the first two price elements. See discussion. Postgresql is currently limited to XPath 1.0 so we need to unnest the XML fragments and then convert them individually to text.

WITH x AS (SELECT
'<promotions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <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 xsi:nil="true"/>
        <price xsi:nil="true"/>
        <price>300</price>
    </promotion>
</promotions>'::xml AS t
)
SELECT (xpath('@promotion-id',node))[1]::text AS "promotion-id", 
       (xpath('/price/text()',
              unnest(xpath('price',node))
       ))[1]::text AS price
  FROM (SELECT unnest(xpath('/promotions/promotion', t)) AS node FROM x) sub

thank you for the message. the proposed solution would have worked but the desired output requires reference to pormotion-id element. Please see the updated message. Appreciate your help. — Rohit.Deepak, Sep 11 '17 at 15:53

Parsing XML document in PostgreSQL using XPATH?

1 Answers1