Sharing experience in querying and operating XML in MySQL database

Mysql has built-in functions for operating xml. They are the ExtractValue() and UpdateXML() functions respectively.

Syntax:

EXTRACTVALUE (fiedname, XPathstring);

The first parameter: fiedname is in String format, which is the field name in the table. The second parameter: XPathstring (string in Xpath format), if you don’t understand Xpath syntax , you can find tutorials online. Function: Return the string containing the queried value from the target XML

UPDATEXML (fiedname, XPathstring, new_value); The first parameter: fiedname is in String format and is the field name in the table. The second parameter: XPathstring (Xpath format string)

The third parameter: new_value, String format, replaces the found qualified data Function: changes the value of the qualified node in the document

Related Recommended mysql video tutorial: "mysql tutorial"

1. First we create a test table.

CREATE TABLE `testtable` (   `testxml` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1

2. Then add a record to the test table. Recorded in xml format.

3. We first use the EXTRACTVALUE function to find out the content of the node named Zhang San. You can see the content of the Zhangsan node we found in the output box below. The xpath format is as follows

extractvalue(testxml,'/Student/Class/Name[self:text()="zhangsan"]'

#4. Now we have to query all the nodes under the class node The value of the name node.

 extractvalue(testxml,'/Student/Class/Name'

5. Next we use the updatexml function to change the node content of xml.

extractvalue(testxml,'/Student/Class/Name[self:text()="zhangsan"]'),  
Updatexml(testxml,'/Student/Class/Name[self:text()="zhangsan"]','updatename')
，把zhangsan节点内容换为updatename。

#6. From the above results, we can see that the xml structure has lost one name node after we use updatexml. We only need to add the updated value to the node when replacing.

extractvalue(testxml,'/Student/Class/Name[self:text()="zhangsan"]'),  
Updatexml(testxml,'/Student/Class/Name[self:text()="zhangsan"]','updatename')

7. Use the Update statement to update the database content.

UPDATE testtableSET testxml=  Updatexml(testxml,&#39;/Student/Class/Name[self:text()="zhangsan"]&#39;,&#39;<Name>updatename</Name>&#39;)

Notes

Using UpdateXml we only changed the content of the found field, and did not Update the database. If you need to update the database, you need to update it with the update statement

The above is the detailed content of Sharing experience in querying and operating XML in MySQL database. For more information, please follow other related articles on the PHP Chinese website!