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