title: SQL Server EXPLICIT mode with FOR XML
body: <p>I am using SQL Server 2008 and I'm trying to produce a custom xml using <code>EXPLICIT</code> mode with <code>FOR XML</code>.</p>
  
  <p>I have the one to many relation having following query</p>
  
  <pre><code>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
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/hQOEk.png" alt="enter image description here"></p>
  
  <p>I want person as parent and address as child may be multiple because people to address having one to many relation.</p>
  
  <p>I wrote the following query</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>Output as follows, it is broken xml with person nested there is some thing wrong with my code for sure.</p>
  
  <pre><code>&lt;person personid="117396" firstname="David"/&gt;
      &lt;person personid="120773" firstname="Doyle"&gt;
          &lt;addr&gt;Mount Rainier&lt;/addr&gt;
          &lt;addr&gt;Annapolis&lt;/addr&gt;
  &lt;/person&gt;
  </code></pre>
  
  <p>Can some one please help me out !!!</p>