How to add a node to xml value in MySql

Question

My sample xml value looks like

    <food>
     <name>Belgian Waffles</name>
     <price>$5.95</price>
     <description>
     Two of our famous Belgian Waffles with plenty of real maple syrup
     </description>
     <calories>650</calories>
    </food>

I need to add this <Shop>KFC</Shop> into the food tag and my output should looks as

  <food>
    <name>Belgian Waffles</name>
    <price>$5.95</price>
    <description>
    Two of our famous Belgian Waffles with plenty of real maple syrup
    </description>
    <calories>650</calories>
    <Shop>KFC</Shop>
  </food>

I tried UpdateXml but behaves differently. Is it possible with the available xml functions in MySql to do this ?

I am new to MySql. Please help me in figure out this.. Thanks in advance..

UPDATE :

I tried this code

select UpdateXML('<food>
 <name>Belgian Waffles</name>
 <price>$5.95</price>
 <description>
 Two of our famous Belgian Waffles with plenty of real maple syrup
 </description>
 <calories>650</calories>
</food>', '/food/Shop', '<Shop>KFC</Shop>');

and in the second parameter i tried instead '/food/Shop' only with '/food' but its not giving the expected results

Why aren't you posting any code that you've used or tried to use? — N.B., Aug 04 '14 at 10:57

score 2 · Accepted Answer · edited May 23 '17 at 11:33

Turns out that it's a bit tricky to do in MySql. You can achieve that by replacing existing <calories> node with <calories> + new <shop> node.

Use ExtractValue() function to get value of existing <calories> and construct <calories>value</calories> block :

set @data = N'<food>
 <name>Belgian Waffles</name>
 <price>$5.95</price>
 <description>
 Two of our famous Belgian Waffles with plenty of real maple syrup
 </description>
 <calories>650</calories>
</food>';

set @data =UpdateXML(@data, 
                     '/food/calories', 
                     concat(
                        '<calories>',
                         ExtractValue(@data, '/food/calories'),
                        '</calories><Shop>KFC</Shop>'
                        )
                    );
select @data

Fiddle demo for this answer

Similar problem : MySQL to update an XML attribute

How to add a node to xml value in MySql

1 Answers1