Update XML column in SQL Server

You can do whatever you want with a XML node. This example shows how to remove a first ocurrence of a entire node and replace it with another one.

declare @test table
(
  Id int not null
, Example xml
)

insert into @test values
(1, '<Legends>
  <Actor>1</Actor>
  <Actor>2</Actor>
  <Actor>3</Actor>
  <Actor>4</Actor>
  <Director>1</Director>
  <Director>9</Director>
  <Writer>1</Writer>
  <Writer>6</Writer>
  <Music>18791</Music>
  <Music>1</Music>
  <Organiser>O-A M K N</Organiser>
</Legends>')

select * from @test

-- Delete node
update @test
set Example.modify('delete (/Legends/Actor[1])')
   where 1 = Example.exist('/Legends/Actor')

declare @newNode XML = '<Actor>22</Actor>';

-- Insert node
update @test
set Example.modify('insert sql:variable("@newNode") as first into (/Legends[1])')


/* Repeat for other nodes like <Director> & <Music>  */
/* Or just replace the entire <Legends> node!        */

select * from @test

Also if you want something really simple just cast the XML to varchar, replace and cast again to XML.

update @test
set Example = CAST(REPLACE(CAST(Example as varchar(max)),'>1</>','>22</') as XML)

If you want to do more complex things you can replace just the value of a simple node, see this link for a good set of examples.