Re: Extract text from XML, pay attention to XML Entities - Mailing list pgsql-general

*ping* anybody interested to reply? Thank You.

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