ronin

Well, it’s tomorrow(plus a few days) so here is a quick intro to using the xml features in Postgres.

First, we need a table, and some data:

create table xml_test (
    id int not null,
    xml text not null
);

insert into xml_test(id, xml) values (1, 
    '<root>
       <child att="a" >child a</child>
       <child att="b" >child b</child>
     </root>');
insert into xml_test(id, xml) values (2, 
     '<root>
        <child att="c" >child c</child>
        <child att="d">child d</child>
      </root>');

To find all the functions you can use:

\df

A couple of useful functions are xpath_bool, which returns true when an xpath query has a result; and xpath_nodeset, which returns a nodeset(go figure) from an xpath.

Lets see how xpath_bool works:

select id 
  from xml_test 
  where xpath_bool(xml, '/root/child[@att="a"]');
 id
 ----
  1
 (1 row)

select id 
  from xml_test 
  where xpath_bool(xml, '/root/child[@att="c"]');
 id
 ----
  2
 (1 row)

and xpath_nodeset doesn’t hold many surprises:

select xpath_nodeset(xml, '//root/child') 
  from xml_test;
 xpath_nodeset
 --------------------------------------------------------------
  <child att="a">child a</child>
    <child att="b">child b</child>
  <child att="c">child c</child>
    <child att="d">child d</child>
 (2 rows)

putting these together:

select xpath_nodeset(xml, '//root/child') 
  from xml_test 
  where
    xpath_bool(xml, '/root/child[@att="a"]');
 xpath_nodeset
 --------------------------------------------------------------
  <child att="a">child a</child><child att="b">child b</child>
 (1 row)

Pretty straight forward stuff.

Note: Excuse the crazy formatting, the default typo theme is whack.

Quick howto on setting up the XML Support in Postgreqsql on Gentoo.

Need to emerge postgresql with xml support on

USE="xml" emerge -vp postgresql

Make sure xml is being used.

[ebuild   R   ] dev-db/postgresql-8.0.8  USE="nls pam perl python 
    readline ssl xml zlib -doc -kerberos -libg++ -pg-hier 
    -pg-intdatetime -tcl -tk" 0 kB

Restart postgresql.

/etc/init.d/postgresql restart

Connect to the database as the admin user so you can install the Xml libraries.

psql -U postgres <dbname>

Install the libraries

\i /usr/share/postgresql/contrib/pgxml.sql

You should see a whole bunch of CREATE FUNCTION lines echo out and then your good to go.

Tomorrow: A quick look at what you can do to get started using it.

For those who want to get started straight away This site has the low down.