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 1laMtm-0000Mq-6p for pgsql-admin@arkaria.postgresql.org; Sat, 24 Apr 2021 18:20:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1laMtk-00087g-7p for pgsql-admin@arkaria.postgresql.org; Sat, 24 Apr 2021 18:20:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1laMtj-00087Z-Nx for pgsql-admin@lists.postgresql.org; Sat, 24 Apr 2021 18:20:55 +0000 Received: from mail-qk1-x736.google.com ([2607:f8b0:4864:20::736]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1laMtc-0004NO-Sr for pgsql-admin@lists.postgresql.org; Sat, 24 Apr 2021 18:20:54 +0000 Received: by mail-qk1-x736.google.com with SMTP id 8so19075318qkv.8 for ; Sat, 24 Apr 2021 11:20:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=LX+5PRrXCGsnmLfR8IqJios2hCbOwbSWIay7D1Rzziw=; b=nWDoE/oa9raKFqtR/vs8njx55FX9kCaEbVeu+DkiOwJoiVstllfbzNfQ/T9Rt3wWZX 28RGgo3g5sRCgIll48ql1L1PrWtXachKO5gGxfliOjeam0fbV15KNO9olLebzPS9RCAC SrGj1v+ASRD243SC4OiXqVwxM/PIIhh8kRiA8Olslhz7dQz1GXa41x4N7fVedRV3Viyu rs01UDMrUQA+sOw13GebmWVSu9NATYAYOqeBgZkH92lUsmS7BKCFtYeJ0aP0bSKTXRKi 3G5tNZmmQ7As/a4poooDsIrE0A/xPAnojVeA4QaPdNDvw4Bmobxtqd4bjmO7tkkz9FET 8E8Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=LX+5PRrXCGsnmLfR8IqJios2hCbOwbSWIay7D1Rzziw=; b=WQ1aYv4HqG8oaFx2dl6ZCh4CvMS3JqxUXTlgco+2VLDVrPddpLQjRG7K/lHiaJAKOo Es7ZxrEpTFR0iP0BwJ3Bv3BOUQYpR0cdV5UGkCAGkl90TkuNdFJTi2admg/53WCBmJQO AQqhaBQe6JwvYUKYVI5bRgLFUMZr0xEPwbBp/8/+adoPk1ifoK+CjZfQPQJ3Kcn+Nt8U qXZQmqCXVTHF/JzM6BChq5U9pclaMPTWgUFYJ31SU7N1khyq2rlRLDTFBr9AdXV9/k1X Szn3eTPOWLhlGIBi6BoVZqkFdngC6G9R2DDvklVyVCxJpJBl8+aFN/zQN2+yEGZ+cow/ hbpw== X-Gm-Message-State: AOAM530bTx9RV7sG36zTmYWbZCm+gTiBksA5XeBbyhELNGs+bqehNxvD nTpkfX4Lkd1tbTNBhizCYmU= X-Google-Smtp-Source: ABdhPJxoe8RrKrGegvvrLMtFw8XbGB4XFBd+CVSQYp6XVZRG3E9wnxLfoL+NkSO/D8CWrI/YWqWekQ== X-Received: by 2002:a05:620a:6ce:: with SMTP id 14mr9890987qky.423.1619288447251; Sat, 24 Apr 2021 11:20:47 -0700 (PDT) Received: from [10.4.4.246] ([191.255.221.248]) by smtp.gmail.com with ESMTPSA id f5sm6834252qkk.12.2021.04.24.11.20.45 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 24 Apr 2021 11:20:46 -0700 (PDT) From: Jorge Silva Message-Id: <09A13721-516D-44B7-B8F9-5DC8A84BA62E@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_68862583-1309-4ECC-80E1-84254BB2F010" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.60.0.2.21\)) Subject: Re: xPath in a database with LATIN1 encoding Date: Sat, 24 Apr 2021 15:20:43 -0300 In-Reply-To: <1707307.1619282961@sss.pgh.pa.us> Cc: Pgsql-admin To: Tom Lane References: <1707307.1619282961@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3654.60.0.2.21) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_68862583-1309-4ECC-80E1-84254BB2F010 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 xpath(=E2=80=98//xml_test/text()=E2=80=99, = convert_from(convert_to(=E2=80=98=C3=A7=C3=A3','utf-8= =E2=80=99),'utf-8')::xml) This line returns the same error, as follows: [Code: 0, SQL State: 2200M] ERROR: could not parse XML document Detail: line 1: Input is not proper UTF-8, indicate encoding ! Bytes: 0xE7 0xE3 0x3C 0x2F =C3=A7=C3=A3 ^ This happens because the =E2=80=9Ctext=E2=80=9D, which is the output = from convert_from() function is encoded with LATIN1, the database = encoding set, and not UTF-8. Is there a way that a text variable is not = encoded as the database encoding set, but some other encoding set, such = as UTF-8? =46rom what I=E2=80=99ve searched for, it 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 wrote: >=20 > 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 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?=20 >=20 > 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 >=20 > ... >=20 > On the whole, the xml type is definitely easier to use with database > encoding set to utf8. I think you'll be paying for encoding = conversion > every time we interact with libxml, for instance. >=20 > regards, tom lane --Apple-Mail=_68862583-1309-4ECC-80E1-84254BB2F010 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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 = xpath(=E2=80=98//xml_test/text()=E2=80=99, = convert_from(convert_to(=E2=80=98<xml_test>=C3=A7=C3=A3</x= ml_test>','utf-8=E2=80=99),'utf-8')::xml)

This = line returns the same error, as follows:
[Code: 0, SQL State: = 2200M]  ERROR: could not parse XML document
  Detail: line 1: Input = is not proper UTF-8, indicate encoding !
Bytes: 0xE7 0xE3 0x3C 0x2F
<xml_test>=C3=A7=C3=A3</xml_test>
        =   ^
This = happens because the =E2=80=9Ctext=E2=80=9D, which is the output = from convert_from() function is encoded with LATIN1, = the database encoding set, and not UTF-8.  Is there a way = that a text variable is not encoded as the database encoding set, but = some other encoding set, such as UTF-8? =46rom what I=E2=80=99ve = searched for, it 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 and =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

=    <?xml encoding=3D"latin1"?> ...

On the whole, the xml type is definitely easier to use with = database
encoding set to utf8.  I think you'll be = paying for encoding conversion
every time we interact with = libxml, for instance.

regards, = tom lane

= --Apple-Mail=_68862583-1309-4ECC-80E1-84254BB2F010--