Re: order by a XML column
От | John Gray |
---|---|
Тема | Re: order by a XML column |
Дата | |
Msg-id | 1016120113.3099.2.camel@adzuki обсуждение исходный текст |
Ответ на | order by a XML column (mclo@asia.com (Chris)) |
Список | pgsql-general |
On Thu, 2002-03-14 at 04:44, Chris wrote: > Hi, > > I have a table with a varchar column which store XML in this format : > > <A>Value of A</A><B>Value of B</B><C>Value of C</C> > A simple solution might be to use PL/perl to pattern match and extract a string, however, if you're willing to turn your rows into well-formed XML documents, by wrapping each in another tag e.g. <doc><A>Value of A</A><B>Value of B</B><C>Value of C</C></doc> then you could try using the code in contrib/xml in the 7.2 distribution, (it requires that you have libxml2 installed) which provides a couple of functions, one of which provides XPath queries. Then you could do: sqltest=# create table xmltest(itemid integer, xmldoc text); sqltest=# insert into xmltest values (1, '<D><A>Man</A><B>Woman</B><C>Child</C></D>'); INSERT 35771 1 sqltest=# insert into xmltest values (2, '<D><A>Tree</A><B>Flower</B><C>Plant</C></D>'); INSERT 35772 1 sqltest=# select itemid from xmltest order by pgxml_xpath(xmldoc, '//D/A/text()','',''); itemid -------- 1 2 (2 rows) sqltest=# select itemid from xmltest order by pgxml_xpath(xmldoc, '//D/B/text()','',''); itemid -------- 2 1 (2 rows) This won't be blindingly fast (there's no caching and indexing and it builds a DOM for each row processed) but it does work, and gives you the full expressive power of XPath queries. Please note, that as it's "contrib" code it is there more as an example, than as a bulletproof, production-grade facility. I did write, but I haven't worked on it for a while (the project I did it for dried up...). You may want to look at the source (which is just a wrapper round libxml2 functions) for inspiration. There's also a README. Note also that in my example I've used text as the datatype. Unless you have a reason for enforcing a maximum size using varchar(), text is a suitable alternative for storing documents. Regards John
В списке pgsql-general по дате отправления: