使用 xpath 从 postgres 中的 XML 列中提取数据 [英] Using xpath to extract data from an XML column in postgres

问题描述

我做了下表:

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

我已将以下数据插入 temp.promotions:

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>

数据在表格中.

我不知道怎么弄出来.我可能希望能够填充我将构建的关系模型，所以我想摆脱所有标签.

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，我们使用的是 Postgres 9.0.4.

FWIW, we are using Postgres 9.0.4.

谢谢，--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

推荐答案

这行得通:

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;

返回:

{"2013 1st Semester Jet Giveaways"}

注意我如何在 xpath() 的第三个参数，并在 xpath 的每个级别使用它.

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.

如果从文档中删除 XML 命名空间，一切都会变得简单得多:

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> 是我自己还是大家都对 json 和 jsonb，所以我们不用处理 XML.</rant>

<rant> Is it just me or is everybody happy about json and jsonb, so we don't have to deal with XML.</rant>

这篇关于使用 xpath 从 postgres 中的 XML 列中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！