Modifying SQL XML сolumn

Question

I have an XML column in one of my table. For example I have an Employee table with following fields:

Name (varhcar) | Address (XML)

The Address field is having values like

<Address>
<Street></Street>
<City></City>
</Address>

I have some n number of rows already in the table. Now I want to insert a new node - Country to all the rows in tha table. With default:

<Country>IND</Country>.

How can I write the query for this. I want all the existing data to be as it is with adding the country node to all the Address column XML.

Answer 1

Try this

SET @XMLDATA.modify(' insert <Country>IND</Country> as last into (Address[1]) ')

Answer 2

update Employee set Address.modify('insert  <Country>IND</Country>
 as last into (/Address)[1]')