title: How do I get multiple XML children inside a single parent node instead of repeating the parent node for each child?
body: <p>I'm trying to produce valid XML for a HL7 CDA document from SQL Server 2008 using FOR XML.  I'm having trouble with the syntax to get multiple children inside a node instead of repeating the node for each child.</p>
  
  <pre><code>/* Expected output:
  &lt;!-- 
  ********************************************************
    Past Medical History section
  ********************************************************
  --&gt;
      &lt;component&gt;
          &lt;section&gt;
              &lt;code code="10153-2" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC"/&gt;
              &lt;title&gt;Past Medical History&lt;/title&gt;
              &lt;text&gt;
                  &lt;list&gt;
                      &lt;item&gt;COPD - 1998&lt;/item&gt;
                      &lt;item&gt;Dehydration: 2001&lt;/item&gt;
                      &lt;item&gt;Myocardial infarction: 2003&lt;/item&gt;
                  &lt;/list&gt;
              &lt;/text&gt;
          &lt;/section&gt;
      &lt;/component&gt;
  */
  
  SELECT      '
     ********************************************************
       Past Medical History section
     ********************************************************
     ' As "comment()",
              '10153-2' AS [section/code/@code], 
              '2.16.840.1.113883.6.1' AS [section/code/@codeSystem], 
              'LOINC' AS [section/code/@codeSystemName], 
              'Past Medical History' AS [section/title],
              (SELECT     [Incident] + ' - ' + [IncidentYear] as [item]
               FROM       [tblSummaryPastMedicalHistory] AS PMH
               WHERE      ([PMH].[Incident] IS NOT NULL) 
                                 AND      ([PMH].[PtUnitNum] = [PatientEncounter].[PtUnitNum])
               FOR XML PATH('list'), TYPE
              ) as [section/text]
  FROM         tblPatientEncounter AS PatientEncounter
  WHERE     (PatientEncounterNumber = 54)
  FOR XML PATH('component'), TYPE
  </code></pre>
  
  <p>Instead of getting the </p>
  
  <pre><code>&lt;text&gt;
    &lt;list&gt;
      &lt;item&gt;blah&lt;/item&gt;
      &lt;item&gt;blah2&lt;/item&gt;
    &lt;/list&gt;
  &lt;/text&gt; 
  </code></pre>
  
  <p>structure from the expected output, I get:</p>
  
  <pre><code>&lt;text&gt;
    &lt;list&gt;
      &lt;item&gt;blah&lt;/item&gt;
    &lt;/list&gt;
    &lt;list&gt;
      &lt;item&gt;blah2&lt;/item&gt;
    &lt;/list&gt;
  &lt;/text&gt; 
  </code></pre>
  
  <p>Can anyone please explain how to format the FOR XML to get the multiple children inside the  node?</p>