MySQL更新XML节点值

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:

这有点与MySQL相关,以更新XML属性,但这次我想更新节点值。我在marcxml列中有以下XMLfragment:

<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:

我想更新控制域值标签001,使其成为基于查询的数字。像这样:

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

I have initially the following mysql query:

我最初有以下mysql查询:

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.

该表是auth_header并且它具有authid作为主键(但我想这无关紧要)并且它具有存储xml的marcxml列。该查询为我提供了“0行受影响”。所以它似乎不起作用。

Thanks in advance and cheers!

在此先感谢并欢呼!

1 个解决方案

#1

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:

看看这里的讨论MySQL更新XML属性和mysql站点https://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_updatexml,查询:

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.

在问题的情况下,xml_target是marcxml。 xpath_expr是'// controlfield [@ tag =“001”]',这是需要编辑的节点。 new_xml是concat,所需的数字和结束语句。最后,where表达式也与上面的xpath表达式相同。

Hence:

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

#1