Description:
Function I'd like to have is the ability to get parts of the XML document for further processing, but as a literal XML string, not values of nodes. Think of it as advanced substring(): I have a huge XML document in database, but I want to work only on it's fragment. I would then select with XPath a node-set and use it in my application (for example by populating SimpleXMLElement object in PHP).
How to repeat:
A mockup example could look like this:
mysql> set @xml = '
'> <?xml version="1.0" encoding="utf-8"?>
'> <root>
'> <node id="1">v1</node>
'> <node id="2">
'> v2
'> <node id="3">v3.1</node>
'> <node id="4">v3.2</node>
'> </node>
'> <node id="5">v3</node>
'> </root>';
mysql> SELECT ExtractXML(@xml,'//node[@id="2"]');
+--------------------------------------------+
| ExtractXML(@xml,'//node[@id="2"]') |
+--------------------------------------------+
|<?xml version="1.0" encoding="utf-8"?> |
|<node id="2"> |
| v2 |
| <node id="3">v3.1</node> |
| <node id="4">v3.2</node> |
|</node> |
+--------------------------------------------+
1 row in set (0.00 sec)
When XPath evaluation returns not well formed XML (eg: no root element) either root element should be added:
mysql> SELECT ExtractXML(@xml,'//node[@id="3"]|//node[@id="4"]');
+----------------------------------------------------+
| ExtractXML(@xml,'//node[@id="3"]|//node[@id="4"]') |
+----------------------------------------------------+
|<?xml version="1.0" encoding="utf-8"?> |
|<root> |
| <node id="3">v3.1</node> |
| <node id="4">v3.2</node> |
|</root> |
+----------------------------------------------------+
1 row in set (0.00 sec)
or just a string should be returned:
mysql> SELECT ExtractXML(@xml,'//node[@id="3"]|//node[@id="4"]');
+----------------------------------------------------+
| ExtractXML(@xml,'//node[@id="3"]|//node[@id="4"]') |
+----------------------------------------------------+ |
|<node id="3">v3.1</node> |
|<node id="4">v3.2</node> |
+----------------------------------------------------+
1 row in set (0.00 sec)
possibly behaviour could be 3rd function param based:
ExtractXML(xml_string, xpath_expression, return_well_formed = false);