how to extract value from xml payload that is stored in a table using PGSQL?

Question

I have a table which has a column xml_payload. It has below data.

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.08">
<BkToCstmrDbtCdtNtfctn>
<GrpHdr>
<MsgId>XX2023050820230508USIn00063461</MsgId>
<CreDtTm>2023-05-08T12:14:49.358Z</CreDtTm>
</GrpHdr>
</BkToCstmrDbtCdtNtfctn>
</Document>

How I redrive CreDtTm node value using pgsql?

SELECT unnest(xpath('./BkToCstmrDbtCdtNtfctn/GrpHdr/CreDtTm/text()', xml_payload))
  from wire.stages

And *what* does not work about it? Is there a syntax error, does it just return the wrong data, or does it return no data? Or something else? — Bergi, Jul 14 '23 at 19:08
Declare the namespace. See this answer. https://stackoverflow.com/a/17800646/3710053 — Siebe Jongebloed, Jul 16 '23 at 11:09

score 0 · Answer 1 · answered Jul 14 '23 at 18:57

Your XML example contains namespace declaration that affects the XPath expression, as it needs to take the namespace into account. To retrieve the text content of the element try query like this one

SELECT unnest(xpath('//*[local-name()="Document"]/*[local-name()="BkToCstmrDbtCdtNtfctn"]/*[local-name()="GrpHdr"]/*[local-name()="CreDtTm"]/text()', xml_data::xml))
FROM stages;

Result

--------------------------
 2023-05-08T12:14:49.358Z
(1 row)

how to extract value from xml payload that is stored in a table using PGSQL?

1 Answers1