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);
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);