SQL Server关联表查询结果转换为指定嵌套XML格式的实现方法

解决方案 #

你可以使用SQL Server的嵌套FOR XML PATH查询来实现自定义层级的XML输出，完整查询语句如下：

SELECT
    -- 外层DataHeader节点属性
    TD.DataType AS [@DataType],
    TD.Data AS [@girodata],
    -- 嵌套SectionHeader节点
    (
        SELECT
            TD2.DataType AS [@DataType],
            TD2.Data AS [@girodata],
            -- 嵌套SectionData节点
            (
                SELECT
                    TD3.DataType AS [@DataType],
                    TD3.Data AS [@girodata]
                FROM TestData TD3
                INNER JOIN TestDataTypes TDT3 ON TD3.DataType = TDT3.DataTypeID
                WHERE TDT3.DateTypeName = 'SectionData'
                FOR XML PATH('SectionData'), TYPE
            )
        FROM TestData TD2
        INNER JOIN TestDataTypes TDT2 ON TD2.DataType = TDT2.DataTypeID
        WHERE TDT2.DateTypeName = 'SectionHeader'
        FOR XML PATH('SectionHeader'), TYPE
    ),
    -- 同级SectionFooter节点
    (
        SELECT
            TD4.DataType AS [@DataType],
            TD4.Data AS [@girodata]
        FROM TestData TD4
        INNER JOIN TestDataTypes TDT4 ON TD4.DataType = TDT4.DataTypeID
        WHERE TDT4.DateTypeName = 'SectionFooter'
        FOR XML PATH('SectionFooter'), TYPE
    ),
    -- 同级DataFooter节点
    (
        SELECT
            TD5.DataType AS [@DataType],
            TD5.Data AS [@girodata]
        FROM TestData TD5
        INNER JOIN TestDataTypes TDT5 ON TD5.DataType = TDT5.DataTypeID
        WHERE TDT5.DateTypeName = 'DataFooter'
        FOR XML PATH('DataFooter'), TYPE
    )
FROM TestData TD
INNER JOIN TestDataTypes TDT ON TD.DataType = TDT.DataTypeID
WHERE TDT.DateTypeName = 'DataHeader'
FOR XML PATH('DataHeader'), TYPE

语法说明 #

• FOR XML PATH('节点名称')可以自定义每个层级的根节点名称
• 字段别名加@前缀会被识别为节点属性，不会生成单独的子节点
• TYPE关键字可以保留子查询的XML格式，避免输出内容被转义为普通字符串
• 按照你需要的嵌套层级，从最外层到最内层逐层编写嵌套子查询即可匹配目标格式

注：你示例中的DataFooter闭合标签误写为GiroFooter，上述查询输出的是符合命名规范的正确闭合标签。

内容的提问来源于stack exchange，提问作者Morten Bach