pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedxPath in a database with LATIN1 encoding
6+ messages / 4 participants
[nested] [flat]
* xPath in a database with LATIN1 encoding
@ 2021-04-24 15:20 Jorge Silva <jorge.silva93@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Jorge Silva @ 2021-04-24 15:20 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Dear all,
We have a database, which is encoded with LATIN1, and we are adding some tables in it to store xml files with the “xml” type. After creating the table and populating it, we use the xPath function to retrieve some data from the XML, but it seems to not work properly, because it returns:
ERROR: could not parse XML document
DETAIL: line 1: Input is not proper UTF-8, indicate encoding !
Bytes: 0xE7 0xE3 0x6F 0x20
50550001974291546464219"><ide><cUF>31</cUF><cNF>54646421</cNF><natOp>Venda produ
The characters that it is not recognizing are both “ç” and “ã” 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?
Thanks in advance.
Regards,
Jorge Luiz Moreira Silva
Polimet Indústria Metalúrgica EIRELI
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: xPath in a database with LATIN1 encoding
@ 2021-04-24 16:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jorge Silva <jorge.silva93@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2021-04-24 16:49 UTC (permalink / raw)
To: Jorge Silva <jorge.silva93@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Jorge Silva <jorge.silva93@gmail.com> writes:
> The characters that it is not recognizing are both “ç” and “ã” 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="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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: xPath in a database with LATIN1 encoding
@ 2021-04-24 18:20 Jorge Silva <jorge.silva93@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jorge Silva @ 2021-04-24 18:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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(‘//xml_test/text()’, convert_from(convert_to(‘<xml_test>çã</xml_test>','utf-8’),'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>çã</xml_test>
^
This happens because the “text”, 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? From what I’ve 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 “ç” and “ã” 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="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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: xPath in a database with LATIN1 encoding
@ 2021-04-25 08:44 Holger Jakobs <holger@jakobs.com>
parent: Jorge Silva <jorge.silva93@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Holger Jakobs @ 2021-04-25 08:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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*xpath(‘//xml_test/text()’,
> convert_from(convert_to(‘<xml_test>çã</xml_test>','utf-8’),'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>çã</xml_test>
> ^
> This happens because the “text”, 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? From what I’ve 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
>> <mailto:tgl@sss.pgh.pa.us>> wrote:
>>
>> Jorge Silva <jorge.silva93@gmail.com
>> <mailto:jorge.silva93@gmail.com>> writes:
>>> The characters that it is not recognizing are both “ç” and “ã”
>>> 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="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
>
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
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: xPath in a database with LATIN1 encoding
@ 2021-04-26 10:19 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Peter Eisentraut @ 2021-04-26 10:19 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org
On 25.04.21 10:44, Holger Jakobs wrote:
> 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.
If you are transmitting an XML datum from the client to the server in
the text protocol, then the encoding declaration is ignored. See this
documentation:
https://www.postgresql.org/docs/devel/datatype-xml.html#id-1.5.7.21.7
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: xPath in a database with LATIN1 encoding
@ 2021-04-26 13:44 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Jorge Silva <jorge.silva93@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Peter Eisentraut @ 2021-04-26 13:44 UTC (permalink / raw)
To: Jorge Silva <jorge.silva93@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 24.04.21 17:20, Jorge Silva wrote:
> We have a database, which is encoded with LATIN1, and we are adding some
> tables in it to store xml files with the “xml” type. After creating the
> table and populating it, we use the xPath function to retrieve some data
> from the XML, but it seems to not work properly, because it returns:
As documented at
<https://www.postgresql.org/docs/devel/datatype-xml.html#id-1.5.7.21.7;,
xpath only works if the server encoding is UTF8.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-04-26 13:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-04-24 15:20 xPath in a database with LATIN1 encoding Jorge Silva <jorge.silva93@gmail.com>
2021-04-24 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-04-24 18:20 ` Jorge Silva <jorge.silva93@gmail.com>
2021-04-25 08:44 ` Holger Jakobs <holger@jakobs.com>
2021-04-26 10:19 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2021-04-26 13:44 ` Peter Eisentraut <peter.eisentraut@enterprisedb.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