Beginning with MySQL 5.1.5, two functions providing basic XPath (XML Path Language) capabilities are available: ExtractValue and UpdateXML. For detailed informations about these 2 functions and for a basic description of XPath see the MySQL manual here: http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
ExtractValue is the "MySQL SELECT" equivalent for XML documents and UpdateXML as its name says is the "MySQL UPDATE" equivalent for XML documents.
Let's take an example: Suppose we have a table named my_table that contains a text field named xml_text and suppose that in the row with row_id = 10 the xml_text field content is this XML fragment:
<person id="1">
<firstname>Paul</firstname>
<lastname>Smith</lastname>
<age>25</age>
</person>
<person id="2">
<firstname>Paul</firstname>
<lastname>Jack</lastname>
<age>36</age>
</person>
<person id="3">
<firstname>Carole</firstname>
<lastname>Lego</lastname>
<age>20</age>
</person>
Selection:
Now, to get the firstname of all persons:
SELECT ExtractValue(xml_text,"//firstname") as fname FROM my_table WHERE row_id=10
+--------------------+
| fname |
+--------------------+
| Paul Paul Carole |
+---------------------+
The "//firstname" means any instance of the firstname tag.
As you see, matching elements are returned as a single space-delimited string.
We can extract also by attribute value. In our case the id value:
SELECT ExtractValue(xml_text,'/person[@id="3"]/firstname') as fname FROM my_table WHERE row_id=10
+--------+
| fname |
+--------+
| Carole |
+--------+
The '/person[@id="3"]/firstname' means the instance of the firstname tag that has a root parent tag named person with id attribute equal to 3.
As you see the ExtractValue function makes the selection from an XML fragment very easy.
What about the update process?
The update process is also very easy. Suppose we want to update the age of all persons to 30:
UPDATE my_table SET xml_text = UpdateXML(xml_text,'//age','<age>30</age>') WHERE row_id=10
and if we want to update only the age of Carole to 21, we use the value of the id attribute:
UPDATE my_table SET xml_text = UpdateXML(xml_text,'/person[@id="3"]/age','<age>21</age>') WHERE row_id=10
Conclusion:
Although the functions ExtractValue and UpdateXML are still under development and the XPath syntax currently has many limitations, you can use them with precotion to simplify the manipulation of your XML rows.
ExtractValue is the "MySQL SELECT" equivalent for XML documents and UpdateXML as its name says is the "MySQL UPDATE" equivalent for XML documents.
Let's take an example: Suppose we have a table named my_table that contains a text field named xml_text and suppose that in the row with row_id = 10 the xml_text field content is this XML fragment:
<person id="1">
<firstname>Paul</firstname>
<lastname>Smith</lastname>
<age>25</age>
</person>
<person id="2">
<firstname>Paul</firstname>
<lastname>Jack</lastname>
<age>36</age>
</person>
<person id="3">
<firstname>Carole</firstname>
<lastname>Lego</lastname>
<age>20</age>
</person>
Selection:
Now, to get the firstname of all persons:
SELECT ExtractValue(xml_text,"//firstname") as fname FROM my_table WHERE row_id=10
+--------------------+
| fname |
+--------------------+
| Paul Paul Carole |
+---------------------+
The "//firstname" means any instance of the firstname tag.
As you see, matching elements are returned as a single space-delimited string.
We can extract also by attribute value. In our case the id value:
SELECT ExtractValue(xml_text,'/person[@id="3"]/firstname') as fname FROM my_table WHERE row_id=10
+--------+
| fname |
+--------+
| Carole |
+--------+
The '/person[@id="3"]/firstname' means the instance of the firstname tag that has a root parent tag named person with id attribute equal to 3.
As you see the ExtractValue function makes the selection from an XML fragment very easy.
What about the update process?
The update process is also very easy. Suppose we want to update the age of all persons to 30:
UPDATE my_table SET xml_text = UpdateXML(xml_text,'//age','<age>30</age>') WHERE row_id=10
and if we want to update only the age of Carole to 21, we use the value of the id attribute:
UPDATE my_table SET xml_text = UpdateXML(xml_text,'/person[@id="3"]/age','<age>21</age>') WHERE row_id=10
Conclusion:
Although the functions ExtractValue and UpdateXML are still under development and the XPath syntax currently has many limitations, you can use them with precotion to simplify the manipulation of your XML rows.