CODEWITHSUNDEEP
sql-serverxml
David Walker
David Walker

Reputation: 1506

How do I get multiple XML children inside a single parent node instead of repeating the parent node for each child?

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.

/* Expected output:
<!-- 
********************************************************
  Past Medical History section
********************************************************
-->
    <component>
        <section>
            <code code="10153-2" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC"/>
            <title>Past Medical History</title>
            <text>
                <list>
                    <item>COPD - 1998</item>
                    <item>Dehydration: 2001</item>
                    <item>Myocardial infarction: 2003</item>
                </list>
            </text>
        </section>
    </component>
*/

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

Instead of getting the 

<text>
  <list>
    <item>blah</item>
    <item>blah2</item>
  </list>
</text>

structure from the expected output, I get:

<text>
  <list>
    <item>blah</item>
  </list>
  <list>
    <item>blah2</item>
  </list>
</text>

Can anyone please explain how to format the FOR XML to get the multiple children inside the node?

Upvotes: 1

Views: 4725

Answers (1)

David Walker
David Walker

Reputation: 1506

Figured this out...

Instead of: 

FOR XML PATH('list'), TYPE) as [section/text]

Changed to:

FOR XML PATH(''), TYPE) as [list/section/text]

Hope this helps someone else out in the future.

Upvotes: 2

Related Questions