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
