Update XML nodes when there are multiple matches in MySQL

Question

I need to remove nodes from XML.

I try to use UpdateXML function.

For example removing node C:

SELECT UpdateXML('<A><B>b_value<C>c_value1</C></B></A>', '/A/B/C', '')

Result:

'<A><B>b_value</B></A>'

But when in XML there are multiples matches, function returns the original xml:

SELECT UpdateXML('<A><B>b_value<C>c_value1</C><C>c_value2</C></B></A>', '/A/B/C', '') 

Result:

'<A><B>b_value<C>c_value1</C><C>c_value2</C></B></A>'

But I need to get such result:

'<A><B>b_value</B></A>'

How I can remove all nodes when there are multiple matches.

Answer 1

12.11 XML Functions :: UpdateXML

...

If no expression matching xpath_expr is found, or if multiple matches are found, the function returns the original xml_target XML fragment. ...

...

A possible workaround is to use a stored procedure:

DELIMITER //

CREATE PROCEDURE `sp_update_xml`(
  `xml` TEXT,
  `path` TEXT
)
BEGIN
  DECLARE `current_item` BIGINT UNSIGNED
    DEFAULT ExtractValue(`xml`,
                         CONCAT('COUNT(', `path`, ')')
                        );
  WHILE `current_item` > 0 DO
    SET `xml` := UpdateXML(`xml`,
                           CONCAT(`path`, '[', `current_item`, ']'),
                           ''
                          ),
        `current_item` := `current_item` - 1;
  END WHILE;
  SELECT `xml`;
END//

DELIMITER ;

CALL `sp_update_xml`(@`xml`, '/A/B/C');

See db-fiddle.