Decoding XML entities in Postgresql

Question

I'm having trouble with xpath() in PostgreSQL. For example...

WITH x(col) AS (
    SELECT '<?xml version="1.0" ?><response><status>ERROR &amp; DATA</status></response>'::xml)
SELECT xpath('//status/text()', col) AS status
FROM   x

I would like this to return ERROR & DATA but instead it returns ERROR & DATA, leaving the XML entity encoded. How can I decode this in Postgres?

I have a bad feeling about this. BUG #7844: xpath missing entity decoding - bug or feature — Martin Burch
– Martin Burch, Commented Jul 8, 2020 at 15:25

Martin Burch · Accepted Answer · 2020-11-26 21:25:48Z

2

+300

Using XMLTABLE:

WITH x(col) AS (
SELECT '<?xml version="1.0" ?><response><status>ERROR &amp; DATA</status></response>'::xml
)
SELECT s.status
FROM x, XMLTABLE('//status' PASSING x.col COLUMNS status TEXT PATH '.') s;

--ERROR & DATA

db<>fiddle

edited Nov 26, 2020 at 21:25

Martin Burch

3,0104 gold badges34 silver badges60 bronze badges

answered Nov 26, 2020 at 20:28

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

Martin Burch Over a year ago

Very nice, I'm glad I asked! This particular syntax only works on version 10 and above. Anything for 9.6 or is that getting too old?

Lukasz Szozda Over a year ago

@MartinBurch Yes, it was introduced in PostgreSQL 10

Collectives™ on Stack Overflow

Decoding XML entities 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