Search code examples
t-sqlsql-server-2012for-xml-path

SQL & XML - reducing duplicate data


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>

Solution

  • 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