Как создать иерархию с FOR XML на SQLServer

Question

Получил следующие 2 таблицы с данными

declare @tab1 table(a int, name varchar(10), street varchar(20))
insert @tab1 values(1,'Bruce', 'my street')
insert @tab1 values(2,'Chuck', 'another street')

declare @tab2 table(a int, b varchar(10))
insert @tab2 values(1,'a')
insert @tab2 values(1,'b')
insert @tab2 values(2,'c')
insert @tab2 values(2,'e')

Я пытаюсь создать документ XML, который должен выглядеть следующим образом

<Person>
    <PersonInfo>
        <Name>Bruce</Name>
        <Street>my street</Street>
    </PersonInfo>
    <PersonSkill>
        <Type>a</Type>
        <Type>b</Type>
    </PersonSkill>
</Person>

Пока мой SQL выглядит так

SELECT name AS "PersonInfo/Name", 
street AS "PersonInfo/Street",
b AS "PersonSkill/Type"
FROM @tab1 t1 JOIN @tab2 t2 ON t1.a = t2.a
WHERE NAME = 'Bruce'
FOR XML PATH('Person') 

И дает этот результат

<Person>
    <PersonInfo>
        <Name>Bruce</Name>
        <Street>my street</Street>
    </PersonInfo>
    <PersonSkill>
        <Type>a</Type>
    </PersonSkill>
</Person>
<Person>
    <PersonInfo>
        <Name>Bruce</Name>
        <Street>my street</Street>
    </PersonInfo>
    <PersonSkill>
        <Type>b</Type>
    </PersonSkill>
</Person>

Как я могу избежать получения 2 документов?

Answer 1

Сделайте коррелированный подзапрос в списке столбцов @tab2,

select T1.name as 'PersonInfo/Name',
       T1.street as 'PersonInfo/Street',
       (
       select T2.b as '*' 
       from @tab2 as T2
       where T2.a = T1.a
       for xml path('Type'), root('PersonSkill'), type
       ) 
from @tab1 as T1
where T1.name = 'Bruce'
for xml path('Person')