MySQL to update an XML node value

Question

This is somewhat related to this MySQL to update an XML attribute but this time I want to update the node value. I have the following XMLfragment which is in marcxml column:

<leader>00227nz  a2200109n  4500</leader>
<controlfield tag="001">1</controlfield>
   ...
<controlfield tag="005">20091210091717.0</controlfield>
        ...

I want to update the controlfield value tag 001 such that it becomes a number based on a query. So like this:

<leader>00227nz  a2200109n  4500</leader>
<controlfield tag="001">10</controlfield>
   ...
<controlfield tag="005">20091210091717.0</controlfield>
        ...

I have initially the following mysql query:

UPDATE auth_header SET marcxml = UpdateXML(marcxml, '//controlfield[@tag="001"]', CONCAT('<controlfield tag="001">', '10', '</controlfield>')) WHERE Extractvalue(marcxml, '//controlfield[@tag="001"]') ='169625';

The table is auth_header and it has authid as primary key (but I guess this does no matter) and it has marcxml column where the xml is stored. The query gives me '0 rows affected.' so it seems it does not work.

Thanks in advance and cheers!

it seemed it worked, I tested with another record, I don't know it's just weird.: UPDATE auth_header SET marcxml = UpdateXML(marcxml, '//controlfield[@tag="001"]', CONCAT('', '10', '' )) WHERE Extractvalue(marcxml, '//controlfield[@tag="001"]') ='169625'; — schnydszch, Oct 06 '15 at 08:52
Note that you can `SELECT UpdateXML(...) WHERE ...;` to test the result of `UpdateXML()` without modifying the database, and that's probably a better thing to try, first. The modified value will be shown (returned as a result-set), instead of written to the db. — Michael - sqlbot, Oct 06 '15 at 09:27

score 0 · Answer 1 · edited May 23 '17 at 12:06

Looking at the discussions here MySQL to update an XML attribute and mysql site https://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_updatexml, the query:

UpdateXML(xml_target, xpath_expr, new_xml)

should do the trick.

The xml_target is marcxml in the question's case. The xpath_expr is '//controlfield[@tag="001"]' which is the node that needs editing. The new_xml is to concat , the digit desired, and the closing statement . And lastly, the where expression is also the same with xpath expression above.

Hence:

UPDATE auth_header SET marcxml = UpdateXML(marcxml, '//controlfield[@tag="001"]', CONCAT('<controlfield tag="001">', '10', '</controlfield>' )) WHERE Extractvalue(marcxml, '//controlfield[@tag="001"]') ='169625';

MySQL to update an XML node value

1 Answers1