title: T-SQL XML Create multiply recurring fields under one parent
body: <p>Trying to create XML with T-SQL. First time I've done this format and I'm not succeeding.</p>
  
  <p>I need this:</p>
  
  <pre><code>&lt;record&gt;
      &lt;MedicalRecordNumber&gt;21QWYZ12345ABC&lt;/MedicalRecordNumber&gt; 
      &lt;DischargeDate&gt;2018-06-01&lt;/DischargeDate&gt;    
      &lt;Procedures&gt;
          &lt;PrincipalProcedure&gt;P01198&lt;/PrincipalProcedure&gt; 
          &lt;OtherProcedure&gt;P03245&lt;/OtherProcedure&gt; 
          &lt;OtherProcedure&gt;P05624&lt;/OtherProcedure&gt; 
      &lt;/Procedures&gt; 
  &lt;/Record&gt;
  </code></pre>
  
  <p>But closest I've come is this:</p>
  
  <pre><code>&lt;record&gt;
      &lt;MedicalRecordNumber&gt;JH26557923&lt;/MedicalRecordNumber&gt;
      &lt;DischargeDate&gt;2018-03-12&lt;/DischargeDate&gt;
      &lt;Procedures code="Procedures"&gt;
          &lt;PrincipalProcedure&gt;abc1234&lt;/PrincipalProcedure&gt;
      &lt;/Procedures&gt;
      &lt;OtherProcedures&gt;
          &lt;OtherProcedure&gt;abc&lt;/OtherProcedure&gt;
      &lt;/OtherProcedures&gt;
      &lt;OtherProcedures&gt;
          &lt;OtherProcedure&gt;def&lt;/OtherProcedure&gt;
      &lt;/OtherProcedures&gt;
  &lt;/record&gt;
  </code></pre>
  
  <p>This is my query, where <code>Encounters</code> is the parent table and <code>Procedures</code> is the child table in a 1:many relationship:</p>
  
  <pre><code>select
      Encounters.MedicalRecordNumber,
      dbo.Encounters.DischargeDate,
      'Procedures' as 'Procedures/@code',
      Encounters.PrimaryProcedure as 'Procedures/PrincipalProcedure',
      (select 
           JHHSProcedures.ProcedureCode as OtherProcedure
       from  
           JHHSProcedures
       where 
           JHHSProcedures.MedicalRecordNumber = Encounters.MedicalRecordNumber
           and JHHSProcedures.DisDate = dbo.Encounters.DischargeDate
       for xml path('OtherProcedures'), type)
  from  
      Encounters
  for xml path('record');
  </code></pre>