How to query XML column in Postgresql?

Question

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

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

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

What am I missing with the XML data type?

Solution

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

From the Postgres documentation for the xpath method:

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).

Comments

[ruipacheco]

Doesn't work. I also tried some variations on your answer.

[The Spartan]

After some experimentation, it looks like you need to include the namespace prefix "mydefns" before every element name you're searching for. I'm going to update my answer to reflect this. Let me know if it works.