pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedExtract text from XML, pay attention to XML Entities
3+ messages / 2 participants
[nested] [flat]
* Extract text from XML, pay attention to XML Entities
@ 2026-09-16 15:35 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 1 reply; 3+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-09-16 15:35 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Extract text from XML, pay attention to XML Entities
@ 2026-09-28 07:14 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 1 reply; 3+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-09-28 07:14 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
*ping* anybody interested to reply? Thank You.
Von: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Gesendet: Mittwoch, 16. September 2026 17:35
An: pgsql-general@lists.postgresql.org
Betreff: Extract text from XML, pay attention to XML Entities
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Extract text from XML, pay attention to XML Entities
@ 2026-09-28 07:26 David G. Johnston <david.g.johnston@gmail.com>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2026-09-28 07:26 UTC (permalink / raw)
To: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Monday, September 28, 2026, Färber, Franz-Josef (StMUK) <
Franz-Josef.Faerber@stmuk.bayern.de> wrote:
> **ping** anybody interested to reply? Thank You.
>
>
I suspect you’ve framed this up fully and correctly and are left with
choosing whichever of your last resorts you deem least offensive. I’d
probably go with a custom text transformer personally; though why you need
to do this, the risks involved, tend to matter for such decisions.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-28 07:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-16 15:35 Extract text from XML, pay attention to XML Entities Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-09-28 07:14 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-09-28 07:26 ` David G. Johnston <david.g.johnston@gmail.com>
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