Thread: order by a XML column

order by a XML column

From
mclo@asia.com (Chris)
Date:
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>

How can I sort the result by this column and the content of one of the tag.

e.g.  The table has 2 rows :
row 1: <A>Man</A><B>Woman</B><C>Child</C>
row 2: <A>Tree</A><B>Flower</B></C>Plant</C>

If I sort by the tag "<B>", the result would be :
row 2
row 1

If I sort by the tag "<A>", the result would be :
row 1
row 2

Thanks & Regards,
Chris

Re: order by a XML column

From
John Gray
Date:
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



Re: order by a XML column

From
Doug McNaught
Date:
mclo@asia.com (Chris) writes:

> 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>
>
> How can I sort the result by this column and the content of one of the tag.

You need to write a custom comparison function and use "ORDER BY ... USING".

-Doug
--
Doug McNaught       Wireboard Industries      http://www.wireboard.com/

      Custom software development, systems and network consulting.
      Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...

Re: order by a XML column

From
Joseph Shraibman
Date:
I think there is something in contrib that deals with xml.

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>
>
> How can I sort the result by this column and the content of one of the tag.
>
> e.g.  The table has 2 rows :
> row 1: <A>Man</A><B>Woman</B><C>Child</C>
> row 2: <A>Tree</A><B>Flower</B></C>Plant</C>
>
> If I sort by the tag "<B>", the result would be :
> row 2
> row 1
>
> If I sort by the tag "<A>", the result would be :
> row 1
> row 2
>
> Thanks & Regards,
> Chris