XML Document Functions

MySQL Tutorials - Herong's Tutorial Examples

∟XML Document Functions

Describes some commonly used XML document functions like, ExtractValue() and UpdateXML().

MySQL supports two built-in functions that allows you to manipulate XML documents.

ExtractValue(xml, path) - Returns the first text value at a given XPath in a given XML document. In other words, the function actually applies "<path>/text()"

UpdateXML(xml, path, repl) - Returns a copy of a given XML document updated by replacing the sub-element identified a given path with a given placement XML document.

Examples of XML document functions, XmlFunctions.sql:

-- XmlFunctions.sql
-- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
--
SELECT ExtractValue('<a><b>X</b><b>Y</b></a>','//b[2]');
SELECT ExtractValue('<a><b>X</b><b>Y</b></a>','/a/b[1]');
SELECT ExtractValue('<a>Hello <b>world</b>!</a>','/a');
SELECT ExtractValue('<a>Hello <b>world</b>!</a>','/a/text()');
SELECT ExtractValue('<a>Hello <b>world</b>!</a>','/a/b');
SELECT ExtractValue('<a>Hello <b>world</b>!</a>','/a/b/text()');

SELECT UpdateXML('<a><b>ccc</b><d></d></a>', '/a', '<e>fff</e>');
SELECT UpdateXML('<a><b>ccc</b><d></d></a>', '/b', '<e>fff</e>');
SELECT UpdateXML('<a><b>ccc</b><d></d></a>', '//b', '<e>fff</e>');
SELECT UpdateXML('<a><b>ccc</b><d></d></a>', '/a/d', '<e>fff</e>');

Output:

ExtractValue('<a><b>X</b><b>Y</b></a>','//b[2]')
Y
ExtractValue('<a><b>X</b><b>Y</b></a>','/a/b[1]')
X
ExtractValue('<a>Hello <b>world</b>!</a>','/a')
Hello  !
ExtractValue('<a>Hello <b>world</b>!</a>','/a/text()')
Hello  !
ExtractValue('<a>Hello <b>world</b>!</a>','/a/b')
world
ExtractValue('<a>Hello <b>world</b>!</a>','/a/b/text()')
world
UpdateXML('<a><b>ccc</b><d></d></a>', '/a', '<e>fff</e>')
<e>fff</e>
UpdateXML('<a><b>ccc</b><d></d></a>', '/b', '<e>fff</e>')
<a><b>ccc</b><d></d></a>
UpdateXML('<a><b>ccc</b><d></d></a>', '//b', '<e>fff</e>')
<a><e>fff</e><d></d></a>
UpdateXML('<a><b>ccc</b><d></d></a>', '/a/d', '<e>fff</e>')
<a><b>ccc</b><e>fff</e></a>