代码之家  ›  专栏  ›  技术社区  ›  David Walker

如何在单个父节点中获取多个XML子节点,而不是为每个子节点重复父节点?

  •  1
  • David Walker  · 技术社区  · 16 年前

    我正在尝试使用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以获取节点中的多个子节点吗?

    1 回复  |  直到 16 年前
        1
  •  2
  •   David Walker    16 年前

    弄明白了。。。

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

    改为:

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