Re: xpath_list() function

[Arturo Perez <aperez(at)hayesinc(dot)com>]

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