Re: Extract text from XML, pay attention to XML Entities

*ping* anybody interested to reply? Thank You.

Von: Färber, Franz-Josef (StMUK) <Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de>
Gesendet: Mittwoch, 16. September 2026 17:35
An: pgsql-general(at)lists(dot)postgresql(dot)org
Betreff: Extract text from XML, pay attention to XML Entities

Dear Mailing List,

Let’s say I want to extract from '<x>foo &lt; bar</x>' the inner text "foo < bar" with XML Entity escape sequences resolved.

First attempt:
================
SELECT (xpath('/x/text()', '<x>foo &lt; bar</x>'))[1]::text

… does not resolve the escape sequences: gives me "foo &lt; bar"

Next attempt:
================
SELECT xpath_string('<x>foo &lt; 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 &lt; 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 &lt; 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