Looking for a way to reduce duplicated data using FOR XML
.
Here's the schema and data:
create table #area (StateId int, State varchar(10), Population int)
go
insert into #area values (1, 'AZ', 10000)
go
create table #person (Id int, FirstName varchar(10), LastName varchar(10), StateFk int)
go
insert into #person values (1, 'Joe', 'Smith', 1), (2, 'Jane', 'Doe', 1)
go
select a.State as '@State'
, a.Population as '@Population'
, p.FirstName as 'Person/FirstName'
, p.LastName as 'Person/LastName'
from #area a
inner join #person p on a.StateId = p.StateFk
where a.Id = 1
for xml path('Record'), root ('RecordSet')
go
Here is the result:
<RecordSet>
<Record State="AZ" Population="10000">
<Person>
<FirstName>Joe</FirstName>
<LastName>Smith</LastName>
</Person>
</Record>
<Record State="AZ" Population="10000">
<Person>
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</Record>
</RecordSet>
But since the #area
data is static, I want to reduce the duplicated data. Using FOR XML
, how could I write ot so the results look like this:
<RecordSet>
<State>AZ</State>
<Population>10000</Population>
<Person>
<FirstName>Joe</FirstName>
<LastName>Smith</LastName>
</Person>
<Person>
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</RecordSet>
or this:
<RecordSet State="AZ" Population="1000">
<Person>
<FirstName>Joe</FirstName>
<LastName>Smith</LastName>
</Person>
<Person>
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</RecordSet>
nm, figured it out with subquery
select a.State as '@State'
, a.Population as '@Population'
, (select p.FirstName as 'Person/FirstName'
, p.LastName as 'Person/LastName'
from #person p
where p.stateFk = a.StateId
for xml path('')
) as 'Persons'
from #area a
for xml path('Record'), root ('RecordSet')
go