CODEWITHSUNDEEP
xmlpostgresql
Martin Burch
Martin Burch

Reputation: 2931

Decoding XML entities in Postgresql

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 &amp; DATA, leaving the XML entity encoded. How can I decode this in Postgres?

Upvotes: 1

Views: 630

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

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

Upvotes: 2

Related Questions