I have two tables like this

declare @Habits_new table(ID int,Habits xml default '<habits></habits>')
declare @Habits_OLD table (ID varchar(50), Habit varchar(50))


Having data Like this

INSERT @Habits_new values(1,'<habits><habit>Drink</habit></habits>')
INSERT @Habits_new values(2,'<habits><habit>snor</habit></habits>')
INSERT @Habits_new values(3,'<habits><habit>walk</habit></habits>')
INSERT @Habits_new
values(4,'<habits><habit>Eat</habit><habit>drink</habit><habit>work</habit><habit>enjoy</habit></habits>')

insert @Habits_OLD values(3, 'Cook')
insert @Habits_OLD values(4, 'Biking')
insert @Habits_OLD values(4, 'walk')

I want to move Data from _old table to _new table.

I tried this query


UPDATE A SET
habits.modify('insert <habit>{sql:column("B.Habit")}</habit>as last
into (/habits)[1] ')
FROM @Habits_new AS A JOIN @Habits_OLD AS B ON A.ID=B.ID


***But this query Insert Only one record for the id 4***

1 <habits><habit>Drink</habit></habits>
2 <habits><habit>snor</habit></habits>
3 <habits><habit>walk</habit><habit>Cook</habit></habits>

4 <habits><habit>Eat</habit><habit>drink</habit><habit>work</habit><habit>enjoy</habit><habit>biking</habit></habits>

Anybody help



Full Sql Query

declare @Habits_new table(ID int,Habits xml default
'<habits></habits>')
declare @Habits_OLD table (ID varchar(50), Habit varchar(50))

INSERT @Habits_new values(1,'<habits><habit>Drink</habit></habits>')
INSERT @Habits_new values(2,'<habits><habit>snor</habit></habits>')
INSERT @Habits_new values(3,'<habits><habit>walk</habit></habits>')
INSERT @Habits_new
values(4,'<habits><habit>Eat</habit><habit>drink</habit><habit>work</habit><habit>enjoy</habit></habits>')

insert @Habits_OLD values(3, 'Cook')
insert @Habits_OLD values(4, 'biking')
insert @Habits_OLD values(4, 'snor')



select * from @Habits_new
select * from @Habits_OLD

UPDATE A SET
habits.modify('insert <habit>{sql:column("B.Habit")}</habit>as last
into (/habits)[1] ')
FROM @Habits_new AS A JOIN @Habits_OLD AS B ON A.ID=B.ID

SELECT * FROM @Habits_new

Did you ever figure out a solution? I have the same problem and have had no
luck finding a way to get it to work.