我正在尝试使用for XML为SQLServer2008中的HL7CDA文档生成有效的XML。我在语法上遇到了问题,无法在一个节点中获取多个子节点,而不是为每个子节点重复该节点。
/* 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
<text>
<list>
<item>blah</item>
<item>blah2</item>
</list>
</text>
结构从预期输出中,我得到:
<text>
<list>
<item>blah</item>
</list>
<list>
<item>blah2</item>
</list>
</text>
有人能解释一下如何格式化XML以获取节点中的多个子节点吗?