6,129
社区成员
发帖
与我相关
我的任务
分享
declare @x xml,@y xml
set @x='<item>
<id>1 </id>
<title>姓名 </title>
<value> </value>
</item>
<item>
<id>2 </id>
<title>年龄 </title>
<value> </value>
</item>
'
set @y='<item>
<id>1 </id>
<value>张三 </value>
</item>
<item>
<id>2 </id>
<value>20 </value>
</item>
'
;with t1
as(
select
D.x.value('./id[1]','int') AS id,
D.x.value('./title[1]','nvarchar(100)') AS title,
D.x.value('./value[1]','nvarchar(100)') AS [value]
from @x.nodes('/*') as D(x)),
t2
as(
select
D.x.value('./id[1]','int') AS id,
D.x.value('./value[1]','nvarchar(100)') AS [value]
from @y.nodes('/*') as D(x))
select a.id,title,b.[value]
from t1 as a
left join t2 as b on a.id = b.id
for xml path('item')
/*
<item>
<id>1</id>
<title>姓名 </title>
<value>张三 </value>
</item>
<item>
<id>2</id>
<title>年龄 </title>
<value>20 </value>
</item>
*/