Select data from xml using mysql ExtractValue()

Question

I want to extract location id from give xml, and also want to put condition that extract it if location does exists.

<?xml version="1.0" encoding="UTF-8"?>
<id>1006221342207</id>
<name>Hitesh Modha</name>
<email>[email protected]</email>
<location>
    <id>115440481803904</id>
    <name>Ahmedabad, India</name>
</location>

I have tried :

SET location = ExtractValue(xml, '//locations//id');    
SELECT ExtractValue(location, '//id'); 

but it is not working. Please help

Answer 1

The SQL

SELECT
  ExtractValue('<?xml version="1.0" encoding="UTF-8"?>
<id>1006221342207</id>
<name>Hitesh Modha</name>
<email>[email protected]</email>
<location>
    <id>115440481803904</id>
    <name>Ahmedabad, India</name>
</location>', '//location//id');

returns only an empty result, because the path //locations//id is empty for the XML fragment you use.

You're more likely looking for '/location/id' for the xpath expression, as it will return the id text() value: 115440481803904

Next mistake you do is that you think ExtractValue would return an XML fragment you can re-run ExtractValue on. That's just not the case (only if - which is not the case in your example - there would have been XML encoded as CDATA in the located text() node).

All the details are explained in detail here:

• http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

If you can not work around these misconceptions, there is no further suggestion I can give you.