Monday, January 16, 2012

[Level 3] XML in MySQL.

#!/usr/bin/bash
createTable() {
  mysql -ujoseph <<EOF
CREATE TABLE persons (
id int auto_increment primary key,
data text
);
EOF
}
#createTable

createUsers(){
  mysql -ujoseph <<EOF
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>
')
;
EOF
}
#createUsers

queryUserTels() {
  mysql -ujoseph <<EOF
select
ExtractValue(data,'//person/tels/tel[1]'),
ExtractValue(data,'/person/tels/tel[2]'),
ExtractValue(data,'person/tels/tel[3]')
from persons
where id=1;
EOF
}
#queryUserTels

updateXML() {
  mysql -ujoseph <<EOF
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;
EOF
}
#updateXML

Wish this helps.  regards, Stanley Huang