From: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Extract text from XML, pay attention to XML Entities
Date: Wed, 16 Sep 2026 15:35:15 +0000
Message-ID: <615fa488caa2422393498300d476dcc0@stmuk.bayern.de> (raw)
Dear Mailing List,
Let's say I want to extract from '<x>foo < bar</x>' the inner text "foo < bar" with XML Entity escape sequences resolved.
First attempt:
================
SELECT (xpath('/x/text()', '<x>foo < bar</x>'))[1]::text
... does not resolve the escape sequences: gives me "foo < bar"
Next attempt:
================
SELECT xpath_string('<x>foo < bar</x>', '/x/text()')
... works as intended, but
* the xml2 extension is deprecated
* won't deal with namespaces as far as I understand
Third attempt:
================
SELECT txt
FROM XMLTABLE('/x' PASSING '<x>foo < bar</x>' COLUMNS txt TEXT PATH '.')
works as intended, but if I want both namespaces and isolate this piece of code into a stored procedure, I have to deal with the XMLTABLE XMLNAMESPACE clause. And I suspect there is no freaking way to pass a parameter containing an array of namespace definitions into the XMLTABLE XMLNAMESPACE clause? (This I would consider a failure of SQL reusability.)
Any idea how to solve this fairly simple problem in a simple and general fashion? Thank You.
My last resorts would be
* using xpath(...)::text and replacing XML Entities *manually*
* or using XMLTABLE with dynamic SQL
* or: SELECT * FROM XMLTABLE('.' PASSING (xpath('/x', '<x>foo < bar</x>'))[1] COLUMNS txt TEXT PATH '.')
(Which shall mean: I can use xpath potentially passing namespace definitions as an array, and then use XMLTABLE for the single purpose of un-escaping the text.
Regards,
Franz-Josef Färber
P.S.: I have read:
Re: xpath does not seem to escape HTML correctly
https://www.postgresql.org/message-id/1409795403248-5817667.post%40n5.nabble.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: Franz-Josef.Faerber@stmuk.bayern.de, pgsql-general@lists.postgresql.org
Subject: Re: Extract text from XML, pay attention to XML Entities
In-Reply-To: <615fa488caa2422393498300d476dcc0@stmuk.bayern.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox