如何在Postgresql中查询XML列？ [英] How to query XML column in Postgresql?

问题描述

我在Postgres中创建了一个包含XML列的表：

I've created a table in Postgres that contains an XML column:

 id           | integer
 date_created | timestamp with time zone
 hash         | character varying(10)
 original     | xml
 report_name  | text

我插入了XML字符串：

I've inserted an XML string:

id |         date_created          |    hash    |                                 original                                 |               report_name                
----+-------------------------------+------------+--------------------------------------------------------------------------+------------------------------------------
  9 | 2017-09-26 17:37:16.823251+02 | aaaaaaaaaa | <RequestReportResponse xmlns="http://mws.amazonaws.com/doc/2009-01-01/">+| _GET_XML_ALL_ORDERS_DATA_BY_LAST_UPDATE_
    |                               |            |   <RequestReportResult>                                                 +| 
    |                               |            |     <ReportRequestInfo>                                                 +| 
    |                               |            |       <ReportType>_GET_XML_ALL_ORDERS_DATA_BY_LAST_UPDATE_</ReportType> +| 
    |                               |            |       <ReportProcessingStatus>_SUBMITTED_</ReportProcessingStatus>      +| 
    |                               |            |       <EndDate>2017-09-26T13:31:02+00:00</EndDate>                      +| 
    |                               |            |       <Scheduled>false</Scheduled>                                      +| 
    |                               |            |       <ReportRequestId>50064017435</ReportRequestId>                    +| 
    |                               |            |       <SubmittedDate>2017-09-26T13:31:02+00:00</SubmittedDate>          +| 
    |                               |            |       <StartDate>2017-09-26T13:31:02+00:00</StartDate>                  +| 
    |                               |            |     </ReportRequestInfo>                                                +| 
    |                               |            |   </RequestReportResult>                                                +| 
    |                               |            |   <ResponseMetadata>                                                    +| 
    |                               |            |     <RequestId>e092cdbe-2978-4064-a5f6-129b88322b02</RequestId>         +| 
    |                               |            |   </ResponseMetadata>                                                   +| 
    |                               |            | </RequestReportResponse>                                                +| 
    |                               |            |                                                                          |

在在线 XPath测试器我能够在 ReportRequestId 中检索值，但是在查询Postgresql时我没有回来的值：

Using the same XML in an online XPath tester I am able to retrieve the value in ReportRequestId but when querying Postgresql I get no values back:

select xpath('/RequestReportResponse/RequestReportResult/ReportRequestInfo/ReportRequestId', original) from amazon_output where hash='aaaaaaaaaa';

XML数据类型缺少什么？

What am I missing with the XML data type?

推荐答案

由于具有XML名称空间（xmlns），因此需要在xpath查询中包括该名称空间：

Since you have an XML namespace (xmlns), you'll need to include that in the xpath query:

select xpath('/mydefns:RequestReportResponse/mydefns:RequestReportResult/mydefns:ReportRequestInfo/mydefns:ReportRequestId',
              original,
              ARRAY[ARRAY['mydefns', 'http://mws.amazonaws.com/doc/2009-01-01/']])
from amazon_output where hash='aaaaaaaaaa';

来自 Postgres文档：

该函数的可选第三个参数是名称空间映射的数组。此数组应为二维文本数组，第二个轴的长度等于2（即，它应为数组的数组，每个数组均由2个元素组成）。每个数组条目的第一个元素是名称空间名称（别名），第二个元素是名称空间URI。不需要此数组中提供的别名与XML文档本身中使用的别名相同（换句话说，在XML文档和xpath函数上下文中，别名都是本地的）。

The optional third argument of the function is an array of namespace mappings. This array should be a two-dimensional text array with the length of the second axis being equal to 2 (i.e., it should be an array of arrays, each of which consists of exactly 2 elements). The first element of each array entry is the namespace name (alias), the second the namespace URI. It is not required that aliases provided in this array be the same as those being used in the XML document itself (in other words, both in the XML document and in the xpath function context, aliases are local).

这篇关于如何在Postgresql中查询XML列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！