使用XML PATH的T-SQL查询会生成重复的父节点

Question

假设我有两个名为tb1和tb2 的表

create table tb1
(
id int
);
insert into tb1 values(100),(200);
create table tb2
(
id int,
col2 int
);
insert into tb2 values(100,1),(100,2),(100,3),(200,3),(200,4);

这是我的SQL代码：

SELECT 
t.id as [@healthAccountRecord]
,(
SELECT t2.col2 as [DiagnosisCode/@code]     
FROM dbo.tb2 as t2 
WHERE t.id = t2.id    
FOR XML PATH('DiagnosisCodes'), TYPE
)
FROM dbo.tb1 as t
FOR XML PATH('Account'), ROOT ('Accounts')

这是我的输出：

<Accounts><Account healthAccountRecord="100"><DiagnosisCodes><DiagnosisCode code="1"/> 
</DiagnosisCodes><DiagnosisCodes><DiagnosisCode code="2"/></DiagnosisCodes><DiagnosisCodes> 
<DiagnosisCode code="3"/></DiagnosisCodes></Account><Account healthAccountRecord="200"> 
<DiagnosisCodes><DiagnosisCode code="3"/></DiagnosisCodes><DiagnosisCodes><DiagnosisCode code="4"/> 
</DiagnosisCodes></Account></Accounts>

以下是我想要的样子：

> "    <Accounts>
>     <Account healthAccountRecord="100"
>       <DiagnosisCodes>
>         <DiagnosisCode code="1" />
>         <DiagnosisCode code="2" />    
>         <DiagnosisCode code="3" />   
>      </DiagnosisCodes>    </Account>
>     <Account healthAccountRecord="200"
>       <DiagnosisCodes>
>         <DiagnosisCode code="3" />
>         <DiagnosisCode code="4" />     
>       </DiagnosisCodes>   <Accounts>
> 
> "

http://sqlfiddle.com/#！18/ab448/1

我该如何做到这一点？

Answer 1

你差不多到了。

SQL

DECLARE @tb1 TABLE (id int);
insert into @tb1 values(100),(200);
DECLARE @tb2 table (id int, col2 int);
insert into @tb2 values(100,1),(100,2),(100,3),(200,3),(200,4);
SELECT 
t.id as [@healthAccountRecord]
,(
SELECT t2.col2 as [DiagnosisCode/@code]     
FROM @tb2 as t2 
WHERE t.id = t2.id    
FOR XML PATH(''), TYPE, ROOT('DiagnosisCodes')
)
FROM @tb1 as t
FOR XML PATH('Account'), ROOT ('Accounts')

输出

<Accounts>
<Account healthAccountRecord="100">
<DiagnosisCodes>
<DiagnosisCode code="1" />
<DiagnosisCode code="2" />
<DiagnosisCode code="3" />
</DiagnosisCodes>
</Account>
<Account healthAccountRecord="200">
<DiagnosisCodes>
<DiagnosisCode code="3" />
<DiagnosisCode code="4" />
</DiagnosisCodes>
</Account>
</Accounts>