Parsing XML document in PostgreSQL using XPATH? -
is there way parse following xml document in postgresql using xpath obtain desired output
--corrected required output
promotion-id price new-promotion null new-promotion null new-promotion 300
for price
element. when run query provided below 300
output. issue query ignores price elements having <price xsi:nil="true"/>
structure.
is there way return null
result price elements <price xsi:nil="true"/>
type structure?
my code like:
with x (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 t ) select unnest(xpath('//price/text()', node))::text (select unnest(xpath('/promotions/promotion', t)) node x) sub
any suggestions above issue highly appreciated.
the problem there no text extracted first 2 price elements. see discussion. postgresql limited xpath 1.0 need unnest xml fragments , convert them individually text.
with x (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 t ) select (xpath('@promotion-id',node))[1]::text "promotion-id", (xpath('/price/text()', unnest(xpath('price',node)) ))[1]::text price (select unnest(xpath('/promotions/promotion', t)) node x) sub
Comments
Post a Comment