xpath_list() function
Hi,
I have installed xml2 contrib to my postgreSQL 8.1.4 install. The
functionality it offer when working with xml files is really good, but i
currently experiencing a few issues with the xpath_list function and a
simple test.
I took a simple xml file (the one described in the PostgreSQL book by Korry
and Susan Douglas), just for testing purposes added an extra film with 2
year tags containing the values 1972 and 1973 . I can extract this
information with the xpath_list function which returns the following
testdb=# SELECT film_name, xpath_list(description, 'year') FROM filminfo;
film_name | xpath_list
--------------- +------------
Casablanca | 1942
Rear Window | 1954
The Godfather | 1972
Test film | 1973,1972
It would seem reasonable in this example that the 1973,1972 gained from
xpath_list could be reused in a WHERE clause like so
SELECT film_name WHERE '1973' IN (xpath_list(description, 'year'));
But the xpath_list function returns a single string
so the above query returns no rows. I can not find a way of splitting
the values returned from xpath_list
so that they can be used in an IN ()
clause, can anybody point me in the right direction for a method that can
split the result, or offer me some advice on how to achieve it.
Thanks in advance,
Andy
Original Message From Andy Dale
Hi,
testdb=# SELECT film_name, xpath_list(description, 'year') FROM filminfo;
film_name | xpath_list
--------------- +------------
Casablanca | 1942
Rear Window | 1954
The Godfather | 1972
Test film | 1973,1972
It would seem reasonable in this example that the 1973,1972 gained from
xpath_list could be reused in a WHERE clause like so
SELECT film_name WHERE '1973' IN (xpath_list(description, 'year'));
How about SELECT film_name WHERE (ARRAY['1973'] <@
STRING_TO_ARRAY((xpath_list(description, 'year')), ',' ); ?
(not tested...)
Regards,
George
Hi,
Unfortunately the suggestion by Geogre did not work, but i solved it like
so:
SELECT film_name FROM filminfo
WHERE '1973' = ANY (STRING_TO_ARRAY((xpath_list(description, 'year')),','))
Thanks,
Andy
Show quoted text
On 22/03/07, George Weaver <gweaver@shaw.ca> wrote:
Original Message From Andy Dale
Hi,
testdb=# SELECT film_name, xpath_list(description, 'year') FROM filminfo;
film_name | xpath_list
--------------- +------------
Casablanca | 1942
Rear Window | 1954
The Godfather | 1972
Test film | 1973,1972It would seem reasonable in this example that the 1973,1972 gained from
xpath_list could be reused in a WHERE clause like soSELECT film_name WHERE '1973' IN (xpath_list(description, 'year'));
How about SELECT film_name WHERE (ARRAY['1973'] <@
STRING_TO_ARRAY((xpath_list(description, 'year')), ',' ); ?(not tested...)
Regards,
George---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
On Thu, 22 Mar 2007 14:36:32 +0100, Andy Dale wrote:
testdb=# SELECT film_name, xpath_list(description, 'year') FROM filminfo;
film_name | xpath_list
--------------- +------------
Casablanca | 1942
Rear Window | 1954
The Godfather | 1972
Test film | 1973,1972It would seem reasonable in this example that the 1973,1972 gained from
xpath_list could be reused in a WHERE clause like soSELECT film_name WHERE '1973' IN (xpath_list(description, 'year'));
But the xpath_list function returns a single string so the above query
returns no rows. I can not find a way of splitting the values returned
from xpath_list
so that they can be used in an IN ()
clause, can anybody point me in the right direction for
For that you're supposed to use xpath_table. I forget the exact syntax
but you end up doing something like
select film_name from
(select xpath_table(...) as FILMS)
where film_year = 1973.
-arturo