Here is a quick nesting example

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH SAMPLE_DATA(Location,Building,RoomName,Habitant )AS

(SELECT * FROM (VALUES

('USA','White House','Oval Office','Mickey Mouse')

,('UK','Whitehall','24hour Lounce','Donald Duck')

) AS X(Location,Building,RoomName,Habitant)

)

,LOCATION_DETAIL(Location,LocationType) AS

(SELECT * FROM (VALUES

('USA','Condinental')

,('UK','Island')

) AS X(Location,LocationType)

)

SELECT

'group' AS '@type'

,'text' AS 'Location/@type'

,(SELECT LD.LocationType FROM LOCATION_DETAIL LD

WHERE LD.Location = SD.Location) AS 'Location/Detail'

,SD.Location

,SD.Habitant AS 'Building/@habitant'

,SD.Building

,SD.RoomName

FROM SAMPLE_DATA SD

FOR XML PATH('Location'),ROOT('Locations');

Results

<Locations>

<Location type="group">

<Location type="text">

<Detail>Condinental</Detail>USA</Location>

<Building habitant="Mickey Mouse">White House</Building>

<RoomName>Oval Office</RoomName>

</Location>

<Location type="group">

<Location type="text">

<Detail>Island</Detail>UK</Location>

<Building habitant="Donald Duck">Whitehall</Building>

<RoomName>24hour Lounce</RoomName>

</Location>

</Locations>