Re: pgxml & xpath_table - Mailing list pgsql-general

From John Gray
Subject Re: pgxml & xpath_table
Date
Msg-id pan.2006.06.10.00.43.34.452230@azuli.co.uk
Whole thread Raw
In response to pgxml & xpath_table  ("Philippe Lang" <philippe.lang@attiksystem.ch>)
Responses Re: pgxml & xpath_table
List pgsql-general
Hi,

On Fri, 09 Jun 2006 08:43:51 +0200, Philippe Lang wrote:
> I'm playing with the contrib/pgxml library under PG 8.1.4, and I'm not sure if what I found with pgxml is a feature
ofa bug: 
>
[snip]
> I get:
>
> --------------------
> id    doc_num     line_num    val1    val2    val3
> 1     C1          L1          1       2       3
> 1                 L2          11      22      33
> --------------------
>
> I was expecting doc_num would receive twice the C1 value, just like with a normal sql join.
>

The results from the XPath expressions should be seen as a plain list
representation of a multivalued answer rather than a join expression. i.e.
This is intended to be a feature. In order to deal with multivalued
results, the xpath_table function as many rows as the largest number of
result values from any of the XPath expressions. There is no sound way to
fill in the other columns if the result sets are of different lengths, so
they are left as null.

The assumption was that the XPath expressions would be used together - the code
doesn't know that /doc/@num only occurs once and that it is equally
applicable for all the rows.

This is the reason why xpath_table allows you to specify an
identifying field  (usually a primary key but doesn't have to be)- the
solution to your question is to join an xpath_table that just fetches the
document number against the primary key, e.g.:

SELECT t.*,i.doc_num FROM
xpath_table('id','xml','test',
   '/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c','1=1')
  AS t(id int4, line_num varchar(10), val1 int4, val2 int4, val3 int4),
xpath_table('id','xml','test','/doc/@num','1=1')
  AS i(id int4, doc_num varchar(10))
WHERE i.id=t.id and i.id=1
ORDER BY doc_num, line_num;

Giving

 id | line_num | val1 | val2 | val3 | doc_num
----+----------+------+------+------+---------
  1 | L1       |    1 |    2 |    3 | C1
  1 | L2       |   11 |   22 |   33 | C1
(2 rows)

Hope this helps.

Regards

John



pgsql-general by date:

Previous
From: David Fetter
Date:
Subject: Re: Fabian Pascal and RDBMS deficiencies in fully
Next
From: Trent Shipley
Date:
Subject: Re: Fabian Pascal and RDBMS deficiencies in fully