Re: BUG #5859: XML result in line and column

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