MySQL UpdateXML with automatic node inserting?

Question

I am writing an application that supports custom fields. Currently I store all custom fields in a XML formated text field ( e.g. '<root><field1>val1</field1><field2>val2</field2></root>' in cust_field)

I am able to to use updateXML(cust_field, '/root/field1', '<field1>new value</field1') to update those values, however if I use updateXML(cust_field, '/root/field3', '<field3>new value</field3>') then it does not work since field3 is not in the old value. Is there a way to let MySQL automatically insert the new field3 node and its value into cust_field? I am thinking about stored procedure or even stored function but not familiar with both, can anyone point me to the right direction?

Answer 1

The MySQL XML Functions will not do this automatically.

You can create a stored function to call UpdateXML() if the element is present, otherwise to add the element using your own logic.

Here's a basic template to get you started:

DELIMITER $$

CREATE FUNCTION update_xml(xml_target text, xpath_expr text, new_xml text) returns text
BEGIN
  DECLARE return_val text;

  IF (ExtractValue(xml_target,xpath_expr) != '')
  THEN
    RETURN updateXML(xml_target,xpath_expr,new_xml); 
  ELSE
    SET return_val := xml_target;
    -- add code here to insert the new element into your XML string
    RETURN return_val;
  END IF;

END $$

Answer 2

I actually ended up putting the logic in my app sql engine.