T-SQL生成指定格式XML：多节点输出与添加XML声明问题

问题2：修改代码同时输出多个Document节点 #

核心思路是在外层对Analysis 1 Code字段做分组，每个分组对应一个Document节点，内层Header、Item查询关联当前分组的Analysis 1 Code值即可，修改后的代码如下：

SELECT 
    (
        SELECT
            -- 生成当前分组对应的Header节点
            (
                SELECT DISTINCT
                    'HR' DocType,
                    x1.[Analysis 1 Code] CompCode,
                    CONVERT(varchar(8), GETDATE(), 112) DocDate,
                    CONVERT(varchar(8), GETDATE(), 112) PostDate,
                    'Civica Cx Rents' RefDocNo,
                    'Civica Cx Rents' Text
                FOR XML PATH('Header'), TYPE
            ),
            -- 生成当前分组对应的所有Item节点
            (
                SELECT
                    ROW_NUMBER() OVER (ORDER BY x2.id) 'ItemNo',
                    'GIAccNo' 'GIAccNo',
                    'TaxCode' 'TaxCode',
                    'Amount' 'Amount',
                    'Currency' 'Currency',
                    'Text' 'Text',
                    'CostCenter' 'CostCenter',
                    'RefKey1' 'RefKey1',
                    'RefKey2' 'RefKey2',
                    'RefKey3' 'RefKey3'
                FROM XML_EXAMPLE x2
                WHERE x2.[Analysis 1 Code] = x1.[Analysis 1 Code]
                FOR XML PATH('Item'), TYPE
            )
        FROM XML_EXAMPLE x1
        GROUP BY x1.[Analysis 1 Code]
        FOR XML PATH('Document'), TYPE
    )
FOR XML PATH(''), ROOT('DocumentBatch'), TYPE

执行后会生成包含1000、2000两个Document节点的完整XML结构。 #

问题1：添加XML头部声明 #

SQL Server原生XML数据类型不支持直接存储XML声明头，所以需要把生成的XML转换为字符串类型后手动拼接声明前缀，完整代码如下：

SELECT 
    '<?xml version="1.0" encoding="iso8859-1"?>' + 
    CAST(
        (
            SELECT 
                (
                    SELECT
                        (
                            SELECT DISTINCT
                                'HR' DocType,
                                x1.[Analysis 1 Code] CompCode,
                                CONVERT(varchar(8), GETDATE(), 112) DocDate,
                                CONVERT(varchar(8), GETDATE(), 112) PostDate,
                                'Civica Cx Rents' RefDocNo,
                                'Civica Cx Rents' Text
                            FOR XML PATH('Header'), TYPE
                        ),
                        (
                            SELECT
                                ROW_NUMBER() OVER (ORDER BY x2.id) 'ItemNo',
                                'GIAccNo' 'GIAccNo',
                                'TaxCode' 'TaxCode',
                                'Amount' 'Amount',
                                'Currency' 'Currency',
                                'Text' 'Text',
                                'CostCenter' 'CostCenter',
                                'RefKey1' 'RefKey1',
                                'RefKey2' 'RefKey2',
                                'RefKey3' 'RefKey3'
                            FROM XML_EXAMPLE x2
                            WHERE x2.[Analysis 1 Code] = x1.[Analysis 1 Code]
                            FOR XML PATH('Item'), TYPE
                        )
                    FROM XML_EXAMPLE x1
                    GROUP BY x1.[Analysis 1 Code]
                    FOR XML PATH('Document'), TYPE
                )
            FOR XML PATH(''), ROOT('DocumentBatch'), TYPE
        ) AS NVARCHAR(MAX)
    ) AS FullXml

注意事项 #

• 若在SSIS中使用该输出，需将输出列类型设置为DT_WSTR或DT_NTEXT，避免字符转义错误。
• 若需要严格匹配iso8859-1编码，可在SSIS导出组件中设置输出文件编码为Western European (ISO)，与声明头的编码规则保持一致。

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