Thread: 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

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,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


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