How to Create a hierarchy with FOR XML on SQLServer

Question

I Got the following 2 tables with data

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')

I'm try to create a XML document that should look like this

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

So far my SQL looks like this

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') 

And gives this result

<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>

How can I avoid getting 2 documents?

Answer 1

Do a correlated sub query in the column list against @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')