MySQL to update an XML attribute

Question

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.

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 Fragment included for clarity:

Original Fragment

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

What I want as the result:

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

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)

score 3 · Answer 1 · answered Jul 29 '16 at 07:01

You can specifically target the attribute you wish re rewrite with the attribute::att axis.

Example MySQL code to verify behavior

SELECT UpdateXML('<root><sub att="foo" xatt="bar">Content Text</sub><sec att="etc">Container</sec></root>', '/root/sub/attribute::att', 'att="something"')

The result of the query will be

<root><sub att="something" xatt="bar">Content Text</sub><sec att="etc">Container</sec></root>

Remember to be specific in your XPATH query, because if multiple targets matches, nothing will be updated. (observed by testing it)

Elegant & simple solution that does not require the element to be rebuilt. Have you thought of contributing this to mySQL documentation Balazs? I also found the shorthand Xpath notation `'root/sub/@att'` also works. — Peter Brand, Jan 04 '17 at 20:48

score 2 · Accepted Answer · answered May 23 '12 at 11:45

2

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.

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;

See it on sqlfiddle.

answered May 23 '12 at 11:45

eggyal

122,705
18
212
237

Cheers, I'de obviously misunderstand the fundamentals of the UpdateXML argument. – Ashimema May 23 '12 at 16:50
For any future reader... the exact query I used to obtain the result required was: `UPDATE biblioitems_temp SET marcxml = UpdateXML(marcxml, '//datafield[@tag="520"]', CONCAT( '', ' ', Extractvalue(marcxml, '//datafield[@tag="520"]/subfield[@code="3"]'), ' ', '' ) ) WHERE Extractvalue(marcxml, '//datafield[@tag="520"]/subfield[@code="3"]') !='';` – Ashimema Jun 01 '12 at 15:05

score 0 · Answer 3 · answered Jul 20 '18 at 13:16

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

Note that the UpdateXML function requires an existing node to be found. If you want to insert an attribute, you have to replace an existing attribute with more than one. for example to insert attribute d in the x element: select updateXML('<x a="aaa" b="bbb">xxxxxx<c>cccc</c></x>', 'x/@a', 'a="aaa" d="ddd"')

MySQL to update an XML attribute

3 Answers3

Linked