Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1laaOK-000257-8h for pgsql-admin@arkaria.postgresql.org; Sun, 25 Apr 2021 08:45:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1laaNm-0002p7-JM for pgsql-admin@arkaria.postgresql.org; Sun, 25 Apr 2021 08:44:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1laaNm-0002oz-AN for pgsql-admin@lists.postgresql.org; Sun, 25 Apr 2021 08:44:50 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1laaNi-00019R-Kq for pgsql-admin@lists.postgresql.org; Sun, 25 Apr 2021 08:44:49 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id E76C9380500; Sun, 25 Apr 2021 10:44:44 +0200 (CEST) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id NM_GIl8UR74J; Sun, 25 Apr 2021 10:44:44 +0200 (CEST) Received: from tuxedo.local.de (dynamic-046-114-106-175.46.114.pool.telefonica.de [46.114.106.175]) by rs.plausibolo.de (Postfix) with ESMTPSA id 864F4380465; Sun, 25 Apr 2021 10:44:44 +0200 (CEST) Subject: Re: xPath in a database with LATIN1 encoding To: pgsql-admin@lists.postgresql.org References: <1707307.1619282961@sss.pgh.pa.us> <09A13721-516D-44B7-B8F9-5DC8A84BA62E@gmail.com> From: Holger Jakobs Message-ID: Date: Sun, 25 Apr 2021 10:44:42 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.10.0 MIME-Version: 1.0 In-Reply-To: <09A13721-516D-44B7-B8F9-5DC8A84BA62E@gmail.com> Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="UU5fDb0PdlZvrHEQ9FNw6PFDoWwpazDQl" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --UU5fDb0PdlZvrHEQ9FNw6PFDoWwpazDQl Content-Type: multipart/mixed; boundary="6b89FBxGXjryIJNcl6g3pWdMuWNGAiKdL"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: Subject: Re: xPath in a database with LATIN1 encoding References: <1707307.1619282961@sss.pgh.pa.us> <09A13721-516D-44B7-B8F9-5DC8A84BA62E@gmail.com> In-Reply-To: <09A13721-516D-44B7-B8F9-5DC8A84BA62E@gmail.com> --6b89FBxGXjryIJNcl6g3pWdMuWNGAiKdL Content-Type: multipart/alternative; boundary="------------6A8D6F6FF6E7383DC4863507" Content-Language: de-DE This is a multi-part message in MIME format. --------------6A8D6F6FF6E7383DC4863507 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Am 24.04.21 um 20:20 schrieb Jorge Silva: > Thanks for the quick reply, Tom. I am trying something simpler. I am=20 > trying to find a way to run the xPath function with a xml file type,=20 > which has latin characters, such as: > *SELECT*xpath(=E2=80=98//xml_test/text()=E2=80=99,=20 > convert_from(convert_to(=E2=80=98=C3=A7=C3=A3','ut= f-8=E2=80=99),'utf-8')*::*xml) > > This line returns=C2=A0the same error, as follows: > [Code: 0, SQL State: 2200M] =C2=A0ERROR: could not parse XML document > =C2=A0 Detail: line 1: Input is not proper UTF-8, indicate encoding ! > Bytes: 0xE7 0xE3 0x3C 0x2F > =C3=A7=C3=A3 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^ > This happens because the=C2=A0=E2=80=9Ctext=E2=80=9D, which is the outp= ut from=20 > convert_from() function is encoded with LATIN1, the=C2=A0database=C2=A0= encoding=20 > set, and not UTF-8. =C2=A0Is there a way that a text variable is not=20 > encoded as the database encoding set, but some=C2=A0other encoding set,=20 > such as UTF-8? From what I=E2=80=99ve searched for,=C2=A0it seems that = it would be=20 > something similar to nvarchar that only exists in Microsoft SQL Server.= > > > >> On 24 Apr 2021, at 13:49, Tom Lane > > wrote: >> >> Jorge Silva > > writes: >>> The characters that it is not recognizing are both =E2=80=9C=C3=A7=E2= =80=9D and =E2=80=9C=C3=A3=E2=80=9D=20 >>> because I think they are encoded differently in latin1 and utf-8. Is = >>> it possible to somehow use the xPath function with special=20 >>> characters in the XML and in a database which is not encoded with=20 >>> utf-8? >> >> I don't have a lot of expertise in this area, but I think you need >> an explicit encoding indicator in the xml header, a la >> >> =C2=A0=C2=A0=C2=A0 ... >> >> On the whole, the xml type is definitely easier to use with database >> encoding set to utf8. =C2=A0I think you'll be paying for encoding conv= ersion >> every time we interact with libxml, for instance. >> >> regards, tom lane > The point is that the XML standard dictates that every document without=20 an explicit encoding, must be encodied in UTF-8. So, whenever you want=20 to use a different encoding, this has to be stated in the XML header, as = indicated in Tom Lane's answer. So the encoding of XML columns isn't actually the one of the database=20 (although UTF-8 is best in any case), but simply is the XML standard.=20 Your client encoding may vary, though, but this would limit the=20 characters which can be transferred. Regards, Holger --=20 Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --------------6A8D6F6FF6E7383DC4863507 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable


Am 24.04.21 um 20:20 schrieb Jorge Silva:
Thanks for the quick reply, Tom. I am trying something simpler. I am trying to find a way to run the xPath function with a xml file type, which has latin characters, such as:
= SELECT<= /span> xpath(=E2=80=98//= xml_test/text()=E2=80=99, convert_from(convert_to= (=E2=80=98<xml_test>=C3=A7=C3=A3</xml_test>','utf-8=E2=80=99),'utf-8')::xml)
=
This line returns=C2=A0the same error, as follows:
[Code: 0,=20 SQL State: 2200M] =C2=A0ERROR: could not parse XML document
=C2=A0 De= tail: line 1: Input is not proper UTF-8, indicate encoding !
Bytes: 0x= E7 0xE3 0x3C 0x2F
<xml_t= est>=C3=A7=C3=A3</xml_test>
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 ^
This happ= ens because the=C2=A0=E2=80=9Ctext=E2=80=9D, which is the outpu= t from convert_from() function is encoded with LATIN1, the=C2=A0database=C2=A0encoding set, and not UTF-8. =C2=A0I= s there a way that a text variable is not encoded as the database encoding set, but some=C2=A0other encoding set, such as UTF= -8? From what I=E2=80=99ve searched for,=C2=A0it seems that it = would be something similar to nvarchar that only exists in Microsoft SQL Server.



On 24 Apr 2021, at 13:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:=

Jorge Silva <jorge.silva93@gmail.com>= ; writes:
The characters that = it is not recognizing are both =E2=80=9C=C3=A7=E2=80=9D an= d =E2=80=9C=C3=A3=E2=80=9D because I think they are encoded differently in latin1 and utf-8. Is it possible to somehow use the xPath function with special characters in the XML and in a database which is not encoded with utf-8?

I don't have a lot of expertise in this area, but I think you need
an explicit encoding indicator in the xml header, a la
=C2=A0=C2=A0=C2=A0<?xml encoding=3D"latin1"?> ...
On the whole, the xml type is definitely easier to use with database
encoding set to utf8. =C2=A0I think you'll be paying for encoding conversion
every time we interact with libxml, for instance.

= = regards,= tom lane

The point is that the XML standard dictates that every document without an explicit encoding, must be encodied in UTF-8. So, whenever you want to use a different encoding, this has to be stated in the XML header, as indicated in Tom Lane's answer.

So the encoding of XML columns isn't actually the one of the database (although UTF-8 is best in any case), but simply is the XML standard. Your client encoding may vary, though, but this would limit the characters which can be transferred.

Regards,

Holger


--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------6A8D6F6FF6E7383DC4863507-- --6b89FBxGXjryIJNcl6g3pWdMuWNGAiKdL-- --UU5fDb0PdlZvrHEQ9FNw6PFDoWwpazDQl Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYIUr+gUDAAAAAAAKCRCKV5yTsxlhurFK AJ43Ow5kgtJ5xX0X4eQW45B7+2ebJgCgycHxf70X7IwFilyLpOSzesBMVk0= =uHLN -----END PGP SIGNATURE----- --UU5fDb0PdlZvrHEQ9FNw6PFDoWwpazDQl--