Looks like there is a bug in MySQL UpdateXML

user_13810710 0
Viewed 34

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 <b>222</b> should supposed to be changed to <e>222</e>. I've already checked every single letter in my code repeatedly.

Help please!...

xml mysql
1 Answers

Your updates are always effectively replacing some <b> tag with an <e> tag at a certain index. 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, the first statement replaces the first <b> tag with an <e> tag. After running the first statement, @xml will look like this:

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

The second statement will start with the above value and then seek and mutate the second <b> tag still present (i.e., the last one):

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

Finally the last statement will start with the value above and try to seek the third <b> tag. Since there is only one <b> tag left (the middle tag), the last 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 <b> tag in @xml. Here's how the value of @xml is updated:

  • After the 1st statement: <a><e>111</e><b>222</b><b>333</b></a>
  • After the 2nd statement: <a><e>111</e><e>222</e><b>333</b></a>
  • After the 3rd statement: <a><e>111</e><e>222</e><e>333</e></a>.
user_6645770
Related