Re: order by a XML column

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