sql server - Reading values from database and updating an existing xml -

using sql server 2008 , table has values this:

empn ename      job       hiredate ---- ---------- --------- ----------- 7341 smith      clerk     17-dec-1980 7482 allen      salesman  20-feb-1981 

my xml in sharefolder

<employees>     <employee>         <empno>7369</empno>         <ename>smith</ename>         <job>clerk</job>         <hiredate>17-dec-1980</hiredate>     </employee>         <employee>         <empno>7499</empno>         <ename>allen</ename>         <job>salesman</job>         <hiredate>20-feb-1981</hiredate>     </employee> </employees> 

now need change existing xml based on database values (empn)

how that?

your question not clear...

some hints @ first:

• avoid culture specific or (even worse!) language specific date/time formats 17-dec-1980. within xml should use iso8601.
• please read how ask sql question , how create mcve

the table data show , xml provide not cover same ids, hence assume, need add new employees new sub-nodes:

declare @existingxml xml= '<employees>   <employee>     <empno>7369</empno>     <ename>smith</ename>     <job>clerk</job>     <hiredate>17-dec-1980</hiredate>   </employee>   <employee>     <empno>7499</empno>     <ename>allen</ename>     <job>salesman</job>     <hiredate>20-feb-1981</hiredate>   </employee> </employees>';  set language english; declare @tmptable table(mpn int,ename varchar(100),job varchar(100),hiredate date); insert @tmptable values  (7341,'smith','clerk','17-dec-1980') ,(7482,'allen','salesman','20-feb-1981');  declare @newxml xml= ( select     (     select e.mpn [empno]           ,e.ename [ename]           ,e.job [job]           ,e.hiredate [hiredate]     @tmptable e     xml path('employee'),type     ) newxml ); set @existingxml.modify(n'insert sql:variable("@newxml") last (/employees)[1]');  select @existingxml; 

if need synchronize table's , and xml's data, i'd shredd whole thing derived table, use merge approach on row-wise data , rebuild xml scratch.