Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1x6rfS-000UGc-1B for pgsql-general@arkaria.postgresql.org; Wed, 16 Sep 2026 15:35:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1x6rfR-0084ev-2X for pgsql-general@arkaria.postgresql.org; Wed, 16 Sep 2026 15:35:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with utf8esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1x6rfR-0084en-10 for pgsql-general@lists.postgresql.org; Wed, 16 Sep 2026 15:35:25 +0000 Received: from rex-mailout.bayern.de ([193.34.207.154]) by makus.postgresql.org with utf8esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1x6rfN-00000000PSI-18ce for pgsql-general@lists.postgresql.org; Wed, 16 Sep 2026 15:35:24 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=bayern.de; i=@bayern.de; s=cc-yang; t=1789572921; x=1805124921; h=from:to:subject:date:message-id:content-type: mime-version; bh=qlvTop78fv1kRWx48ZgMa6AeqULvxo7qhzhJ5c8yOnI=; b=j0HmQynEgSoKz7xiIc2EnNQvdRcep5Dnk3sTc4QqC31FtPBIHDZ6Qf8v oUh3bihjH9W8oInzJCoNGxB9yBeaIAAUfB1W2loXmwnopOkxkKLE8fQA1 MhWciV/u+IHI9s0nWiywHAnj3h2m35q5nOFnibgzn/3jMqwS33AOUhrnw zL2MefCwY1yp5KNIqTevhn2JZ6JVm7kJhVP0A7K5kI2NOnSHcwmFPHq2K +3DH5qVdeHIEbFNNVqR1lnWPye21gt188SON3etalFYaIC1bvvMzvPnIg YQw+MyLSO99F8FKAP0b6csSdhf64M6F8VLxIpI5SmNRhjXnGpymfjUx34 w==; X-CSE-ConnectionGUID: nG6IQc9YQYCn/ZeyboguXA== X-CSE-MsgGUID: UYJj2sXAR7GAxn3hCESWrQ== Received: from unknown (HELO rin135.bayern.de) ([10.197.20.135]) by rex134.bayern.de with ESMTP/TLS/TLS_AES_256_GCM_SHA384; 16 Sep 2026 17:35:16 +0200 X-CSE-ConnectionGUID: ZZi1xMQHSFyYSPAGYXjNTA== X-CSE-MsgGUID: 7LamDLQuSw+HiHbSVScjpg== X-Talos-CUID: =?us-ascii?q?9a23=3AoTeyLmhj/XLYi0dE/C2CwrSgYTJuQE/clWf1Gm+?= =?us-ascii?q?DJ3dYc4W8Zm6g+oVYup87?= X-Talos-MUID: 9a23:ji19dwbJcZk/q+BT6BvlgR1GDdpT/OeOCWAHqqs9sMucOnkl X-BYBN-routing: post Received: from zmx-ej4-p25852.max.bayern.de ([10.173.241.201]) by rin135.bayern.de with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 16 Sep 2026 17:35:16 +0200 Received: from ZMX-EJ2-P25850.max.bayern.de (10.173.241.200) by ZMX-EJ4-P25852.max.bayern.de (10.173.241.201) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.2562.46; Wed, 16 Sep 2026 17:35:16 +0200 Received: from ZMX-EJ2-P25850.max.bayern.de ([10.173.241.200]) by ZMX-EJ2-P25850.max.bayern.de ([10.173.241.200]) with mapi id 15.02.2562.046; Wed, 16 Sep 2026 17:35:16 +0200 From: =?iso-8859-1?Q?F=E4rber=2C_Franz-Josef_=28StMUK=29?= To: "pgsql-general@lists.postgresql.org" Subject: Extract text from XML, pay attention to XML Entities Thread-Topic: Extract text from XML, pay attention to XML Entities Thread-Index: Ad1F8KSd2OxdQuZwSb2WzUw0T5EN5g== Date: Wed, 16 Sep 2026 15:35:15 +0000 Message-ID: <615fa488caa2422393498300d476dcc0@stmuk.bayern.de> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.171.65.1] Content-Type: multipart/alternative; boundary="_000_615fa488caa2422393498300d476dcc0stmukbayernde_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_615fa488caa2422393498300d476dcc0stmukbayernde_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear Mailing List, Let's say I want to extract from 'foo < bar' the inner text "foo = < bar" with XML Entity escape sequences resolved. First attempt: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D SELECT (xpath('/x/text()', 'foo < bar'))[1]::text ... does not resolve the escape sequences: gives me "foo < bar" Next attempt: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D SELECT xpath_string('foo < bar', '/x/text()') ... works as intended, but * the xml2 extension is deprecated * won't deal with namespaces as far as I understand Third attempt: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D SELECT txt FROM XMLTABLE('/x' PASSING 'foo < bar' 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 contain= ing 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 fa= shion? 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', 'foo < bar'= ))[1] COLUMNS txt TEXT PATH '.') (Which shall mean: I can use xpath potentially passing namespace definition= s as an array, and then use XMLTABLE for the single purpose of un-escaping = the text. Regards, Franz-Josef F=E4rber P.S.: I have read: Re: xpath does not seem to escape HTML correctly https://www.postgresql.org/message-id/1409795403248-5817667.post%40n5.nabbl= e.com --_000_615fa488caa2422393498300d476dcc0stmukbayernde_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Dear Mailing List,

 

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

 

First attempt:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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

 

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

 

 

Next attempt:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

SELECT xpath_string('<x>foo &lt; bar</x= >', '/x/text()')

 

… works as intended, but

  • the xml2 extension is deprecated
  • won’t dea= l with namespaces as far as I understand

 

 

Third attempt:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

SELECT txt

FROM XMLTABLE('/x' PASSING '<x>foo &lt; ba= r</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 th= e XMLTABLE XMLNAMESPACE clause. And I suspect there is no freaking way to p= ass a parameter containing an array of namespace definitions into the XMLTABLE XMLNAMESPACE clause? (This I wo= uld 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 Entit= ies *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 definition= s as an array, and then use XMLTABLE for the single purpose of un-escaping = the text.

 

 

Regards,

Franz-Josef F=E4rber

 

 

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

 

--_000_615fa488caa2422393498300d476dcc0stmukbayernde_--