[Level 2] MySQL XML Functions.

One day, a friends of mine asked me if MySQL support XML?
Actually, MySQL havs two XML functions, called "ExtractValue" and "UpdateValue".
You can use these two function as the following samples:

mysql> CREATE TABLE persons (
id int auto_increment primary key,
data text
);

mysql> INSERT INTO persons(data) values
('
<person>
  <name>stanley</name>
  <sex>m</sex>
  <addr>taipei</addr>
  <tels>
    <tel>12340001</tel>
    <tel>12340002</tel>
    <tel>12340003</tel>
  </tels>
</person>
'),
('
<person>
  <name>joseph</name>
  <sex>m</sex>
  <addr>taipei</addr>
  <tels>
    <tel>12350001</tel>
    <tel>12350002</tel>
    <tel>12350003</tel>
  </tels>
</person>
');

mysql> select
ExtractValue(data,'//person/tels/tel[1]'),
ExtractValue(data,'/person/tels/tel[2]'),
ExtractValue(data,'person/tels/tel[3]')
from persons
where id=1\G

*************************** 1. row ***************************
ExtractValue(data,'//person/tels/tel[1]'): 12340001
ExtractValue(data,'/person/tels/tel[2]'): 12340002
ExtractValue(data,'person/tels/tel[3]'): 12340003

mysql> select
data,
ExtractValue(data,'//person/name'),
UpdateXML(data,'//person/name','<name>christy</name>'),
ExtractValue(UpdateXML(data,'//person/name','<name>christy</name>'),'//person/name')
from persons
where id=1\G

*************************** 1. row ***************************

data: stanleymtaipei123400011234000212340003

ExtractValue(data,'//person/name'): stanley

UpdateXML(data,'//person/name','<name>christy</name>'): <name>christy</name>mtaipei123400011234000212340003
ExtractValue(UpdateXML(data,'//person/name','<name>christy</name>'),'//person/name'): christy

Ref:
http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
http://www.w3.org/TR/xpath
http://www.zvon.org/xxl/XPathTutorial/General/examples.html

Wish this helps.

regards,
Stanley Huang