MySQL UpdateXML fails when run from command line

Question

I need to update xml in a mysql table from a bash script so when I try this from the command line:

mysql -uadmin -p**** -h127.0.0.1 db -e "UPDATE my_table set my_field = updatexml(my_field, '/config/properties/property[@name="search_property"]', '<property name="search_property" value="new_property_value" />') where name='my_xml_record';"

` I get the following error :

ERROR 1105 (HY000) at line 1: XPATH error: comparison of two nodesets is not supported: '=search_property]'

However when I run the same function in mysql console directly it works.

mysql> UPDATE my_table set my_field = updatexml(my_field, '/config/properties/property[@name="search_property"]', '<property name="search_property" value="new_property_value" />') name='my_xml_record';

Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

What could be wrong here, the error doesn't make sense to me as it executes successfully in the mysql console.

Answer 1

I found the issue here, the error was very misleading, what happened was this was running a bash script with

eval $command_written_above

this would actually misinterpret the quotes and remove the double quotes inside the single quoted fields. i.e.

mysql -uadmin -p**** -h127.0.0.1 db -e "UPDATE my_table set my_field = updatexml(my_field, '/config/properties/property[@name=search_property]', '<property name=search_property value=new_property_value />') where name='my_xml_record';"

notice the previously double quoted strings are no longer double quoted. When I removed eval and just ran it as a mysql command it ran successfully.