Creating Nested XML File in SQL Server

Question

I have 3 tables and I need to join the 3 tables in order to create a nested XML file

The tables are Events, UserEvents and Users. Here is an example schema:

create table [dbo].[Users]
(
  UserID int,
  Username VARCHAR(MAX)
);


insert into [dbo].[Users] (UserID, Username)  values
(155, 'jweldz'),
(218, 'pwarner'),
(310, 'jeffrey')


create table dbo.Events
(
  [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NULL,
    [Title] [nvarchar](250) NOT NULL,
    [EventStart] [datetime] NOT NULL,
);

SET IDENTITY_INSERT [Events] ON
INSERT INTO [dbo].[Events] ([Id], [UserId], [Title],  [EventStart]) 
VALUES (3409, NULL, 'Boxing Match', '2014-10-05 00:00:00.000')


create table dbo.UserEvents
(
  UserID int,
  EventID int,
);



INSERT INTO [dbo].[UserEvents] ([UserID], [EventID]) VALUES (155, 3409), (218, 3409), (310, 3409)

SQL Fiddle

Using this Schema I'd like to create a nested XML output in the following format (Or something along these lines!) in SQL Server 2014. Every User in an Event is listed to one Event Tag:

<EventList>
  <Event eventid="3409">
    <Title>Boxing Match</Title>
    <Player>
      <UserID>155</UserID>
      <Username>jweldz</Username>
      <UserID>218</UserID>
      <Username>pwarner</Username>
      <UserID>310</UserID>
      <Username>jeffrey</Username>
    </Player>
    <EventStart>2016-04-16T09:00:00</EventStart>
  </Event>
</EventList>

But instead I get:

 <EventList>
      <Event eventid="3409">
        <Title>Boxing Match</Title>
        <Player>
          <UserID>155</UserID>
          <Username>jweldz</Username>
        </Player>
        <EventStart>2016-04-16T09:00:00</EventStart>
      </Event>
      <Event eventid="3409">
        <Title>Boxing Match</Title>
        <Player>
          <UserID>218</UserID>
          <Username>pwarner</Username>
        </Player>
        <EventStart>2016-04-16T09:00:00</EventStart>
      </Event>
</EventList>
....

The Event is repeated each time there is a new player but this isn't ideal. How can I nest the players in one Event Tag using SQL Server 2014?

Answer 1

This query should deliver what you need:

SELECT e.Id AS [@eventid]
      ,e.Title
      ,(
        SELECT u.UserID
              ,u.Username
        FROM dbo.Users AS u
        INNER JOIN dbo.UserEvents AS ue ON u.UserID=ue.UserID
        WHERE e.Id=ue.EventID
        FOR XML PATH(''),TYPE
      ) AS Player
      ,e.EventStart
FROM dbo.Events AS e
FOR XML PATH('Event'),ROOT('EventList')

The result

<EventList>
  <Event eventid="3409">
    <Title>Boxing Match</Title>
    <Player>
      <UserID>155</UserID>
      <Username>jweldz</Username>
      <UserID>218</UserID>
      <Username>pwarner</Username>
      <UserID>310</UserID>
      <Username>jeffrey</Username>
    </Player>
    <EventStart>2014-05-10T00:00:00</EventStart>
  </Event>
</EventList>

But would highly advise you to keep your users separated! With this:

SELECT e.Id AS [@eventid]
      ,e.Title
      ,(
        SELECT u.UserID AS [@userid]
              ,u.Username AS [@username]
        FROM dbo.Users AS u
        INNER JOIN dbo.UserEvents AS ue ON u.UserID=ue.UserID
        WHERE e.Id=ue.EventID
        FOR XML PATH('User'),TYPE
      ) AS Player
      ,e.EventStart
FROM dbo.Events AS e
FOR XML PATH('Event'),ROOT('EventList')

... you'd get this

<EventList>
  <Event eventid="3409">
    <Title>Boxing Match</Title>
    <Player>
      <User userid="155" username="jweldz" />
      <User userid="218" username="pwarner" />
      <User userid="310" username="jeffrey" />
    </Player>
    <EventStart>2014-05-10T00:00:00</EventStart>
  </Event>
</EventList>