pg 9.0 有 xml 属性 ~

我喜欢用 xpaht 查询 数据库 : 
http://www.w3school.com.cn/xpath/xpath_syntax.asp


安装 ： 
wget http://wwwmaster.postgresql.org/redir/391/f/source/v9.0.0/postgresql-9.0.0.tar.gz

./configure --with-libxml 
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test



参考 ：
http://www.postgresql.org/docs/9.0/static/functions-xml.html

/usr/local/pgsql/bin/psql test
> SELECT xmlcomment('hello');
> CREATE TABLE test2 ( id int PRIMARY KEY,xdoc xml);
> INSERT INTO test VALUES (2, '<pmn/>');
> INSERT INTO test VALUES (1, '<pmn>歌曲1</pmn>');
> update test set xdoc=xmlconcat(xdoc,'<pmid>歌曲1_ID</pmid>') where id=1;
> INSERT INTO test VALUES (3, '<pmn>歌曲2</pmn><pmid>歌曲2_ID</pmid>');
> select xmlagg(xdoc) from test where id<>2;
                                   xmlagg                                   
----------------------------------------------------------------------------
 <pmn>歌曲1</pmn><pmid>歌曲1_ID</pmid><pmn>歌曲2</pmn><pmid>歌曲2_ID</pmid>


> select xmlelement(name music,xdoc) from test where id<>2;
                     xmlelement                      
------------------------------------------------------
 <music><pmn>歌曲1</pmn><pmid>歌曲1_ID</pmid></music>
 <music><pmn>歌曲2</pmn><pmid>歌曲2_ID</pmid></music>
(2 rows)


> select 
  xpath('//pmn/text()',
    xmlelement( name musics, 
                     xmlagg( xmlelement(name music,xdoc) )
    ) 
   )
from test where id<>2;
     xpath     
---------------
 {歌曲1,歌曲2}