MySQL操作XML格式字段

MySQL常用命令

参考：https://blog.csdn.net/hejiajunhs/article/details/84487945

-- Example #E1
SELECT extractValue ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/sites/site/name' ) AS VALUE;


-- Example #E2
SELECT extractValue ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/*/*/name' ) AS VALUE;


-- Example #E3
SELECT extractValue ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/sites/site/child::*' ) AS VALUE;
SELECT extractValue ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/descendant::*' ) AS VALUE;
SELECT extractValue ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/sites/site/parent::*' ) AS VALUE;
SELECT extractValue ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/sites/site/name/ancestor::*' ) AS VALUE;
SELECT extractValue ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/sites/site/url/self::*' ) AS VALUE;


-- Example #E4
SELECT extractValue ( '<book><title>SQL:1999</title><author><initial>J</initial><surname>Melton</surname></author></book>', '/book/author/surname[self:text()>="Melton"]' ) AS VALUE;


-- Example #U1
SELECT UpdateXML ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/sites/site/name', 'GG' ) AS VALUE


-- Example #U2
SELECT extractvalue ( UpdateXML ( '<sites><site><name>Google</name><url>www.google.com</url></site></sites>', '/sites/site/name', '<name>GG</name>' ), '/sites/site/name' ) AS value;