使用 xpath 从 postgres 中的 XML 列中提取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17799790/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using xpath to extract data from an XML column in postgres
提问by Scott Wood
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:
我已将以下数据插入到 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.
FWIW，我们使用的是 Postgres 9.0.4。
Thanks, --sw
谢谢，--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
回答by Erwin Brandstetter
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 aliasnfor your namespace in the third argument of xpath()and use it at every level of the xpath.
请注意我如何在第三个参数中为您的命名空间分配命名空间别名，并在 xpath 的每个级别使用它。nxpath()
If you remove the XML namespace from the document, everything becomes much simpler:
如果从文档中删除 XML 命名空间，一切都会变得简单得多：
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 jsonand jsonb, so we don't have to deal with XML.</rant>
<rant>是只有我还是每个人都对jsonandjsonb感到高兴，所以我们不必处理 XML。</rant>
