Generate XML comments with SQL FOR XML statement

Question

Background: I am generating pieces of a much larger XML document (HL7 CDA documents) using SQL FOR XML queries. Following convention, we need to include section comments before this XML node so that when the nodes are reassembled into the larger document, they are easier to read.

Here is a sample of the 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>

Here is the SQL FOR XML statement that I have constructed to render the above XML:

SELECT     '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].[PatientUnitNumber] = [PatientEncounter].[PatientUnitNumber])
       FOR XML PATH('list'), TYPE
      ) as "section/text"
FROM         tblPatientEncounter AS PatientEncounter
WHERE     (PatientEncounterNumber = 6)
FOR XML PATH('component'), TYPE

While I can insert the comments from the controlling function that reassembles these XML snippets into the main document, our goal is to have the comments be generated with the output to avoid document construction errors.

I've tried a few things, but am having trouble producing the comments with the SELECT statement. I've tried a simple string, but have not been able to get the syntax for the line breaks. Any suggestions?

Answer 1

Example:

SELECT [EmployeeKey]
      ,[ParentEmployeeKey]
      ,[FirstName]
      ,[LastName]
      ,[MiddleName]
      ,[DepartmentName] AS "comment()"
  FROM [AdventureWorksDW2008].[dbo].[DimEmployee]
  FOR XML PATH('Employee'),ROOT('Employees')

produces:

<Employees>
  <Employee>
    <EmployeeKey>1</EmployeeKey>
    <ParentEmployeeKey>18</ParentEmployeeKey>
    <FirstName>Guy</FirstName>
    <LastName>Gilbert</LastName>
    <MiddleName>R</MiddleName>
    <!--Production-->
  </Employee>
  <Employee>
    <EmployeeKey>2</EmployeeKey>
    <ParentEmployeeKey>7</ParentEmployeeKey>
    <FirstName>Kevin</FirstName>
    <LastName>Brown</LastName>
    <MiddleName>F</MiddleName>
    <!--Marketing-->
  </Employee>
</Employees>