Using xpath to retrieve data from an XML column in postgres

Question

I made the following table:

create table temp.promotions_xml(id serial promotion_xml xml); 

I entered the following data in temp.promotions:

 <promotions xmlns="http://www.demandware.com/xml/impex/promotion/2008-01-31"> <campaign campaign-id="2013-1st-semester-jet-giveaways"> <description>2013 1st Semester Jet Giveaways</description> <enabled-flag>true</enabled-flag> <start-date>2013-01-01T05:00:00.000Z</start-date> <end-date>2013-07-01T04:00:00.000Z</end-date> <customer-groups> <customer-group group-id="Everyone"/> </customer-groups> </campaign> </promotions> 

The data are given in the table.

I can’t figure out how to do this. I probably want to be able to populate the relational model that I will build, so I want to get rid of all the tags.

Below are a few queries that I have tried that do not work. I am pretty sure that I'm just dancing around the correct syntax. These queries return rows of empty sets.

FWIW, we are using Postgres 9.0.4.

Thanks, --sw

 select xpath('/promotions/campaign/description/text()',promotion_xml) textcol from temp.promotions_xml select xpath('./promotions/campaign/description/text()',promotion_xml) textcol from temp.promotions_xml select xpath('promotions/campaign/description/text()',promotion_xml) textcol from temp.promotions_xml select xpath('///description/text()',promotion_xml) textcol from temp.promotions_xml select xpath('//description/text()',promotion_xml) textcol from temp.promotions_xml select xpath('.//description/text()',promotion_xml) textcol from temp.promotions_xml select xpath('./campaign/description/text()',promotion_xml) textcol from temp.promotions_xml select xpath('//campaign/description/text()',promotion_xml) textcol from temp.promotions_xml 

Answer 1

It works:

 WITH tbl(p_xml) AS ( -- CTE just to provide test table with xml value SELECT '<promotions xmlns="http://www.demandware.com/xml/impex/promotion/2008-01-31"> <campaign campaign-id="2013-1st-semester-jet-giveaways"> <description>2013 1st Semester Jet Giveaways</description> <enabled-flag>true</enabled-flag> <start-date>2013-01-01T05:00:00.000Z</start-date> <end-date>2013-07-01T04:00:00.000Z</end-date> <customer-groups> <customer-group group-id="Everyone"/> </customer-groups> </campaign> </promotions>'::xml ) -- end of CTE, the rest is the solution SELECT xpath('/n:promotions/n:campaign/n:description/text()', p_xml , '{{n,http://www.demandware.com/xml/impex/promotion/2008-01-31}}') FROM tbl; 

Return:

 {"2013 1st Semester Jet Giveaways"} 

Notice how I assign the namespace alias n to your namespace in the third argument to xpath() and use it at each xpath level.

If you remove the XML namespace from the document, things will become much simpler:

 WITH tbl(p_xml) AS ( -- not the missing namespace below SELECT '<promotions> <campaign campaign-id="2013-1st-semester-jet-giveaways"> <description>2013 1st Semester Jet Giveaways</description> <enabled-flag>true</enabled-flag> <start-date>2013-01-01T05:00:00.000Z</start-date> <end-date>2013-07-01T04:00:00.000Z</end-date> <customer-groups> <customer-group group-id="Everyone"/> </customer-groups> </campaign> </promotions>'::xml ) SELECT xpath('/promotions/campaign/description/text()', p_xml) FROM tbl; 

<rant> Is it just me or everyone is happy with json and jsonb , so we don’t need to deal with XML. </rant>