I am using SQL Server 2008 and I'm trying to produce a custom xml using EXPLICIT
mode with FOR XML
.
I have the one to many relation having following query
select
p.personid, p.firstname, a.P_City
from
tblPeople p with (nolock)
left outer join
tblAddresses a with (nolock) on p.personid = a.personid
where
p.personid = 120773
I want person as parent and address as child may be multiple because people to address having one to many relation.
I wrote the following query
select 1 as TAG,
null as parent,
p.personid as [person!1!personid],
p.FirstName as [person!1!firstname],
null as [addr!2!]
from tblPeople p with (nolock) where p.PersonID in (120773,117396)
union all
select 2,1,
p.PersonID, p.FirstName, a.P_City from tblAddresses a with(nolock), tblPeople p
where p.PersonID=a.PersonID and p.PersonID in (120773,117396)
for xml explicit
Output as follows, it is broken xml with person nested there is some thing wrong with my code for sure.
<person personid="117396" firstname="David"/>
<person personid="120773" firstname="Doyle">
<addr>Mount Rainier</addr>
<addr>Annapolis</addr>
</person>
Can some one please help me out !!!
I would recommend to ditch FOR XML EXPLICIT
and use FOR XML PATH/ROOT
instead.
Using this query here:
select
p.personid AS "@ID",
p.firstname, p.LastName,
(
SELECT AddressID AS "@ID", City
FROM dbo.Address a
WHERE a.PersonID = p.PersonID
FOR XML PATH('addr'), TYPE
) AS Addresses
from
Person p with (nolock)
where
p.personid = 120773
FOR XML PATH('Person'), ROOT('People')
you'll get this output XML:
<People>
<Person ID="120773">
<firstname>John</firstname>
<LastName>Doyle</LastName>
<Addresses>
<addr ID="1">
<City>Annapolis</City>
</addr>
<addr ID="2">
<City>Mount Rainier</City>
</addr>
</Addresses>
</Person>
</People>
Tweak it as necessary. Read more details about FOR XML PATH
on MSDN.