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.
