Insert values into XML column child node Sql Server

Question

I have a table1

Deptno  Skills

1       1
1       2
1       3
2       1
2       3
3       1
3       2

I need to insert the skills value as a child node to an XMl column in table2 with respect to matching Deptno

Tried using

UPDATE 
   Dept
SET 
   XMLColumn.modify('insert ('Skills 2 /Skills)
   as last into
   (/Skills[1])
   ') where deptno = 1

But how can I use other table values and nest them into table2 xml column?

Mikael Eriksson · Accepted Answer · 2014-04-23 06:11:38Z

1

update T2
set XMLColumn.modify('insert sql:column("T1.Skills") as last into Skills[1]')
from Table2 as T2
  cross apply (
              select T1.Skills
              from Table1 as T1
              where T2.Deptno = T1.Deptno
              for xml path(''), type
              ) as T1(Skills)
where T2.Deptno = 1

Build the XML you want to insert in a cross apply correlated on Deptno and use sql:column() to pull the generated XML into your modify() statement.

answered Apr 23, 2014 at 6:11

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert values into XML column child node Sql Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related