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?

The Spartan · Accepted Answer · 2017-09-26 18:29:14Z

9

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

edited Sep 26, 2017 at 18:29

answered Sep 26, 2017 at 18:12

The Spartan

4311 gold badge3 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ruipacheco Over a year ago

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

The Spartan Over a year ago

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.

Collectives™ on Stack Overflow

How to query XML column in Postgresql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related