Thread: BUG #5859: XML result in line and column
The following bug has been logged online:
Bug reference: 5859
Logged by: Alexandre
Email address: ultrago@gmail.com
PostgreSQL version: 8.4.7
Operating system: Windows 7
Description: XML result in line and column
Details:
The name of the xml tag is not being updated, becoming the first name
registry for all. Here is example:
Create table AUDITORIA_ANTERIOR
(
SEQUENCIA Serial NOT NULL,
SEQUENCIA_AUDITORIA Integer NOT NULL,
REGISTRO Text NOT NULL,
primary key (SEQUENCIA,SEQUENCIA_AUDITORIA)
);
insert into auditoria_anterior (sequencia_auditoria, registro)
values (1, '<?xml version="1.0" encoding="iso-8859-1"?><ROOT>
<CLIENTES><CGC_CPF_CLIENTE>12345678901</CGC_CPF_CLIENTE><TIPO>1</TIPO><CODIG
O_CONVENIO></CODIGO_CONVENIO><PESSOA>F</PESSOA><NOME>JOAO DA
SILVA</NOME></CLIENTES> </ROOT>')
SELECT * FROM
xpath_table('sequencia',
'registro',
'auditoria_anterior',
'name(/ROOT/CLIENTES/*)|/ROOT/CLIENTES/*',
'sequencia = 1')
AS t(sequencia integer, coluna text, valor text)
--Incorrect result-----------------------------------
sequencia coluna valor
integer text text
--------------------------------------------------
1 CGC_CPF_CLIENTE 12345678901
1 CGC_CPF_CLIENTE 1
1 CGC_CPF_CLIENTE F
1 CGC_CPF_CLIENTE JOAO DA SILVA
--Result that would be right to be presented----------
sequencia coluna valor
integer text text
--------------------------------------------------
1 CGC_CPF_CLIENTE 12345678901
1 TIPO 1
1 PESSOA F
1 NOME JOAO DA SILVA
"Alexandre" <ultrago@gmail.com> writes:
> SELECT * FROM
> xpath_table('sequencia',
> 'registro',
> 'auditoria_anterior',
> 'name(/ROOT/CLIENTES/*)|/ROOT/CLIENTES/*',
> 'sequencia = 1')
> AS t(sequencia integer, coluna text, valor text)
> --Incorrect result-----------------------------------
> sequencia coluna valor
> integer text text
> --------------------------------------------------
> 1 CGC_CPF_CLIENTE 12345678901
> 1 CGC_CPF_CLIENTE 1
> 1 CGC_CPF_CLIENTE F
> 1 CGC_CPF_CLIENTE JOAO DA SILVA
> --Result that would be right to be presented----------
> sequencia coluna valor
> integer text text
> --------------------------------------------------
> 1 CGC_CPF_CLIENTE 12345678901
> 1 TIPO 1
> 1 PESSOA F
> 1 NOME JOAO DA SILVA
I can reproduce this on Fedora 13 (with libxml2-2.7.7-1.fc13.x86_64).
I traced through it and find that libxml returns a simple XPATH_STRING
result for the name() query, whereas the second query returns an
XPATH_NODESET containing the four values shown. So either this behavior
is correct, or it's a libxml bug not ours. I don't know XPath well
enough to know whether name() ought to return more than one value here.
Note that there are some other pretty serious known issues in this
same area, see
http://archives.postgresql.org/pgsql-hackers/2010-02/msg02424.php
Overall it seems like the libxml API is not designed to handle multiple
parallel queries, or maybe we just don't know how to use it for that.
regards, tom lane
On 01/02/11 15:20, Alexandre wrote:
> The following bug has been logged online:
>
> Bug reference: 5859
> Logged by: Alexandre
> Email address: ultrago@gmail.com
> PostgreSQL version: 8.4.7
> Operating system: Windows 7
> Description: XML result in line and column
> Details:
>
> The name of the xml tag is not being updated, becoming the first name
> registry for all. Here is example:
>
> Create table AUDITORIA_ANTERIOR
> (
> SEQUENCIA Serial NOT NULL,
> SEQUENCIA_AUDITORIA Integer NOT NULL,
> REGISTRO Text NOT NULL,
> primary key (SEQUENCIA,SEQUENCIA_AUDITORIA)
> );
>
> insert into auditoria_anterior (sequencia_auditoria, registro)
> values (1, '<?xml version="1.0" encoding="iso-8859-1"?><ROOT>
> <CLIENTES><CGC_CPF_CLIENTE>12345678901</CGC_CPF_CLIENTE><TIPO>1</TIPO><CODIG
> O_CONVENIO></CODIGO_CONVENIO><PESSOA>F</PESSOA><NOME>JOAO DA
> SILVA</NOME></CLIENTES> </ROOT>')
>
> SELECT * FROM
> xpath_table('sequencia',
> 'registro',
> 'auditoria_anterior',
> 'name(/ROOT/CLIENTES/*)|/ROOT/CLIENTES/*',
> 'sequencia = 1')
> AS t(sequencia integer, coluna text, valor text)
>
> --Incorrect result-----------------------------------
> sequencia coluna valor
> integer text text
> --------------------------------------------------
> 1 CGC_CPF_CLIENTE 12345678901
> 1 CGC_CPF_CLIENTE 1
> 1 CGC_CPF_CLIENTE F
> 1 CGC_CPF_CLIENTE JOAO DA SILVA
>
Yep, I can duplicate this under Linux. However this is not a bug in
xpath_table. The problem is your XPath statement - the function name()
always returns the element name of the first element in a list. See
http://www.w3.org/TR/xpath/#section-Node-Set-Functions for details.
Unfortunately the only way to achieve what you want with with XPath 2.0
or XQuery, but libxslt (the underlying library executing the XPath) does
not and does not plan to support XPath 2.0 and PostgreSQL won't support
XQuery any time soon
(http://wiki.postgresql.org/wiki/XML_Support#Future_Projects).
Regards,
--
Mike Fowler
Registered Linux user: 379787