Using xpath to extract data from an XML column in postgres

Question

I've made the following table:

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

I've inserted the following data into 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 is in the table.

I can't figure out how to get it out. I probably will want to be able to populate a relational model that I will build, so I want to get rid of all the tag's.

Below are a few queries that I've tried that don't work. I pretty sure that I am 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

Erwin Brandstetter · Accepted Answer · 2016-11-02T15:17:53.083

This 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;

Returns:

{"2013 1st Semester Jet Giveaways"}

Note how I assign the namespace alias n for your namespace in the third argument of xpath() and use it at every level of the xpath.

If you remove the XML namespace from the document, everything becomes 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 is everybody happy about json and jsonb, so we don't have to deal with XML.</rant>

Great answer, worked for me when trying to xpath out info with namespaces involved! I agree parsing json can be a lot easier as well. — jhnclvr, Sep 25 '14 at 19:28
It's not just you. But it's not me... in all cases. Sometimes XML is just better: https://blogs.oracle.com/xmlorb/entry/analysis_of_json_use_cases — btiernay, Apr 03 '15 at 01:36
Having read the oracle blog, I'm even more convinced that JSON is always the way :-). jsonb is a great addition to Postsgres. — John Powell, Mar 08 '17 at 13:00

Using xpath to extract data from an XML column in postgres

1 Answers1

Linked