Creating Nested XML File in SQL Server
<p>I have 3 tables and I need to join the 3 tables in order to create a nested XML file</p>

<p>The tables are Events, UserEvents and Users. Here is an example schema:</p>

<pre><code>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)
</code></pre>

<p><a href="http://sqlfiddle.com/#!6/861974" rel="nofollow">SQL Fiddle</a></p>

<p>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: </p>

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

<p>But instead I get: </p>

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

<p>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?</p>