xpath_list() function

From: "Andy Dale" <andy(dot)dale(at)gmail(dot)com>
To: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Subject: xpath_list() function
Date: 2007-03-22 13:36:32
Message-ID: faa313130703220636m5dac0e31x2e5c079834bd3118@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2007-03-22 13:40:10 Re: Dealing with table names in functions
Previous Message Greg Sabino Mullane 2007-03-22 13:28:35 Re: Dealing with table names in functions