Looks like there is a bug in MySQL UpdateXML-CodePudding

I tried this query in Workbench and wonder why the second update //b[2] did not change?

SET @xml = '<a><b>111</b><b>222</b><b>333</b></a>';

SELECT
    UpdateXML(@xml, '/a/b[1]',  
    Concat('<e>', Extractvalue(@xml,'//b[1]'),'</e>')) INTO @xml;

SELECT
    UpdateXML(@xml, '/a/b[2]',  
    Concat('<e>', Extractvalue(@xml,'//b[2]'),'</e>')) INTO @xml;

SELECT
    UpdateXML(@xml, '/a/b[3]',  
    Concat('<e>', Extractvalue(@xml,'//b[3]'),'</e>')) INTO @xml;

SELECT @xml

Here's the result:

 ---------------------------------------- 
| @xml                                   |
 ---------------------------------------- 
|'<a><e>111</e><b>222</b><e>333</e></a>' |
 ----------------------------------------

The middle 222 should supposed to be changed to <e>222</e>. I've already checked every single letter in my code repeatedly.

Help please!...

CodePudding user response：

Your updates are always effectively replacing the first  tag with an <e> tag. Changes to @xml are cumulative. I.e., each statement is receiving the value of @xml updated by the previous statement.

Since you are mutating @xml, you first statement will replace the first  with an <e>. After running the first statement, @xml will look like this:

<a><e>111</e><b>222</b><b>333</b></a>

Your second statement will start with the above value and then seek and mutate the second  that it can find (i.e., the last one in the sequence):

<a><e>111</e><b>222</b><e>333</e></a>

Finally you last statement will start with the value above and try to seek the third . Since there is only one  left (the middle tag), the third statement does nothing.

Here's a working version:

SET @xml = '<a><b>111</b><b>222</b><b>333</b></a>';

SELECT
    UpdateXML(@xml, '/a/b[1]',  
    Concat('<e>', Extractvalue(@xml,'//b[1]'),'</e>')) INTO @xml;

SELECT
    UpdateXML(@xml, '/a/b[1]',  
    Concat('<e>', Extractvalue(@xml,'//b[1]'),'</e>')) INTO @xml;

SELECT
    UpdateXML(@xml, '/a/b[1]',  
    Concat('<e>', Extractvalue(@xml,'//b[1]'),'</e>')) INTO @xml;

SELECT @xml

In the version above, the code is always replacing the first  in @xml. Here's how the value of XML is updated:

After the 1st statement: <a><e>111</e>222333</a>
After the 2nd statement: `111222333
After the 3rd statement: <a><e>111</e><e>222</e><e>333</e></a>.