Bug #26060 Create ExtractXML() function to get parts of XML tree
Submitted: 4 Feb 2007 2:36 Modified: 4 Feb 2007 13:02
Reporter: Marcin Kurzyna Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: XML functions Severity:S4 (Feature request)
Version:5.2 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: FeatureRequest, XML, XPath

[4 Feb 2007 2:36] Marcin Kurzyna
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);
[4 Feb 2007 13:02] Valeriy Kravchuk
Thank you for a reasonable feature request.