MySQL更新XML属性 [英] MySQL to update an XML attribute

问题描述

在数据加载中，似乎有些XML属性映射不正确，我现在正在尝试更正此问题，但在MySQL对XML列的处理方面遇到了困难.

In data load, it seems some XML attributes mapped incorrectly and I'm now trying to correct this, but am struggling with MySQL's handling of this XML column.

我想更正所有出现的带有子字段(属性为'code ="3"')的字段(属性为'tag ="520"')的XML属性(非值).下面的查询返回受影响的0行，找到1行.有关如何实现此目标的任何线索.

I want to correct the XML attributes (Not Values) for all occurrences of a field (with attribute 'tag="520"') with subfield (with attribute 'code="3"'). The query below returns 0 rows affected, 1 rows found. Any clues as to how to achieve this.

UPDATE biblioitems
SET marcxml = UpdateXML(marcxml,'datafield[@tag="520"]/subfield[@code="3"]',
                     'datafield[@tag="520"][@ind1="3"]/subfield[@code="a"]')
WHERE biblionumber = '220405';

为清楚起见，包含了XML片段:

XML Fragment included for clarity:

原始片段

<datafield tag="300" ind1=" " ind2=" ">
  <subfield code="f">article</subfield>
</datafield>
<datafield tag="520" ind1=" " ind2=" ">
  <subfield code="3">A description of something here</subfield>
</datafield>
<datafield tag="655" ind1=" " ind2=" ">
  <subfield code="a"></subfield>
</datafield>

我想要的结果是

<datafield tag="300" ind1=" " ind2=" ">
  <subfield code="f">article</subfield>
</datafield>
<datafield tag="520" ind1="3" ind2=" ">
  <subfield code="a">A description of something here</subfield>
</datafield>
<datafield tag="655" ind1=" " ind2=" ">
  <subfield code="a"></subfield>
</datafield>

无法找出如何突出显示代码块中的更改(这是tag ="520"数据字段中的ind1属性及其关联的子字段属性)

Couldn't work out how to highlight the change in a code block (it's the ind1 attribute in the tag="520" datafield and it's associated subfield attributes)

推荐答案

The third argument to UpdateXML should be the new XML fragment with which to replace the portion of the document matched by the XPath given in the second argument.

您可以使用 ExtractValue 创建XML片段:

You can create the XML fragment using ExtractValue:

UPDATE biblioitems
SET    marcxml = UpdateXML(marcxml,
         'datafield[@tag="520"]',
         CONCAT(
           '<datafield tag="520" ind1="a" ind2="',
              ExtractValue(marcxml, 'datafield[@tag="520"]/attribute::ind2'),
           '">',
           '  <subfield code="a">',
             ExtractValue(marcxml, 'datafield[@tag="520"]/subfield'),
           '  </subfield>',
           '</datafield>'
         )
       )
WHERE  biblionumber = 220405;

在 sqlfiddle 上查看.

这篇关于MySQL更新XML属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！