ms sql server 一个xml插入到另一个xml

1.首先定义

declare @xml1 xml,@xml2 xml,@xml3 xml
 set @xml1='<root>
 <book>kl</book>
 <user>rang</user>
 <leng>33</leng>
 <asign>52134</asign>
 </root>'
set @xml2='<root>
 <row>
 <Default>1</Default>
 <Col_DBName>book</Col_DBName>
 </row>
 <row>
 <Default>1</Default>
 <Col_DBName>leng</Col_DBName>
 </row>
 </root>'

2.结果要求

结果@xml2为:

set @xml2='<root>
 <row>
 <Default>1</Default>
 <Col_DBName>book</Col_DBName>===>说明:leng==@xml1中的book节点名称
 <val>kl</val>
 </row>
 <row>
 <Default>1</Default>
 <Col_DBName>leng</Col_DBName>===>说明:leng==@xml1中的leng节点名称
<val>33</val>
 </row>
 </root>' 

3.t_sql如下:

set @xml1=convert(xml,'<root>'+(select t.v.query('.') from @xml1.nodes('root/*') t(v) where t.v.value('local-name(.)','varchar(50)') in
(
 select b.s.value('.','varchar(50)') from @xml2.nodes('//Col_DBName') b(s)
) for xml path(''))+'</root>')
set @xml3=convert(xml,@xml1.query('for $colName in root/* return
 <item>set @xml2.modify(''insert <val>{data($colName)}</val> after (//Col_DBName[text()="{local-name($colName)}"])[1]'');</item>'))
 declare @sql nvarchar(max)
 set @sql=convert(varchar(max),@xml3.query('/item/text()'))
 set @sql=REPLACE(@sql,'<','<')
 set @sql=REPLACE(@sql,'>','>')
 --select @sql

--执行sql

 EXEC sp_executesql @sql,N'@xml2 xml OUTPUT', @xml2 OUTPUT

--最终结果

 select @xml2