Create XML comments using the SQL FOR XML statement

Question

Reference Information. I generate fragments of a much larger XML document (CDA HL7 documents) using SQL FOR XML queries. Following the convention, we should include comments on the section before this XML node so that when the nodes are assembled into a larger document, they are easier to read.

Here is an example of the expected result:

<!-- 
********************************************************
  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 created to display 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 comments from the management function that collects these XML fragments into the main document, our goal is to create comments with the result in order to avoid document construction errors.

, SELECT. , . ?

Answer 1

:

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

:

<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>