xml - 如何在 Postgresql 中查询 XML 列？

Question

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

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

我插入了一个 XML 字符串:

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>                                                +| 
    |                               |            |                                                                          |

在 online 中使用相同的 XML XPath 测试器我能够在 ReportRequestId 中检索值，但是在查询 Postgresql 时我没有得到任何值:

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

我缺少 XML 数据类型的什么？

Answer 1

因为您有一个 XML 命名空间 (xmlns)，您需要将其包含在 xpath 查询中:

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 documentation对于 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).