if OBJECT_ID('tempdb..#Person') IS NOT NULL DROP TABLE #Person
if OBJECT_ID('tempdb..#Company') IS NOT NULL DROP TABLE #Company
if OBJECT_ID('tempdb..#Address') IS NOT NULL DROP TABLE #Address
create table #Person ( Id int, Name varchar(max))
create table #Company( Id int, PersonId int, CompanyName varchar(max))
create table #Address( Id int, CompanyId int, Address varchar(max))

insert into #Person values(1, 'Jon Smith')
insert into #Company values(1, 1, 'Oracle')
insert into #Address values(1, 1, 'Oracle address 1')
insert into #Address values(2, 1, 'Oracle address 2')

insert into #Company values(2, 1, 'Microsoft')
insert into #Address values(3, 2, 'Microsoft address 1')
insert into #Address values(4, 2, null)


select #Person.Name, #Company.CompanyName, #Address.Address
from #Person inner join #Company on #Person.Id = #Company.PersonId
inner join #Address on #Company.Id = #Address.CompanyId

--attribute centric xml
select #Person.Name, #Company.CompanyName, #Address.Address
from #Person inner join #Company on #Person.Id = #Company.PersonId
inner join #Address on #Company.Id = #Address.CompanyId
for xml auto, root('results')
--element centric xml
select Person.Name, Company.CompanyName, Address.Address
from #Person Person inner join #Company Company on Person.Id = Company.PersonId
inner join #Address Address on Company.Id = Address.CompanyId
for xml auto, root('results')

--hierarchical  result
select Person.Name, Company.CompanyName, Address.Address
from #Person Person inner join #Company Company on Person.Id = Company.PersonId
inner join #Address Address on Company.Id = Address.CompanyId
for xml auto, elements, root('results')

--hierarchical result2
select Person.Name, Address.Address, Company.CompanyName
from #Person Person inner join #Company Company on Person.Id = Company.PersonId
inner join #Address Address on Company.Id = Address.CompanyId
for xml auto, root('results')