如何在SQL Server中用游标创建XML变量并动态追加XML内容？

这个需求完全可行，以下是对应你预期流程的具体实现步骤和代码示例：

实现步骤与代码 #

1. 声明XML变量与游标
   先创建空XML变量，同时定义游标遍历目标表名：

DECLARE @ResultXML XML = '';
DECLARE @TableName NVARCHAR(128);

DECLARE TableCursor CURSOR FOR
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES
    -- 可添加过滤条件，比如只处理特定 schema 的表
    WHERE TABLE_SCHEMA = 'dbo';

2. 打开游标并循环处理
   遍历每个表名，生成动态SQL并执行，将结果追加到XML变量：

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @DynamicSQL NVARCHAR(MAX);
    DECLARE @TableXML XML;

    -- 生成动态SQL，用QUOTENAME处理表名避免注入和特殊字符问题
    SET @DynamicSQL = N'
        SELECT
            ''' + @TableName + ''' AS ''TABLE/@path'',
            (SELECT
                 t1.id AS ''value/@id'',
                 t1.name AS ''value'',
                 t2.name AS ''value_jp''
             FROM 
                 ' + QUOTENAME(@TableName) + ' t1
             JOIN ' + QUOTENAME(@TableName + '_locale') + ' t2 
                 ON t1.ID = t2.FK_Id 
             WHERE 
                 t2.FK_Locale = 38
             FOR XML PATH(''''), ROOT(''rows''), TYPE)
        FOR XML PATH('''')';

    -- 执行动态SQL并获取当前表的XML结果
    EXEC sp_executesql @DynamicSQL, N'@TableXML XML OUTPUT', @TableXML OUTPUT;

    -- 将当前表的XML追加到结果变量中
    SET @ResultXML.modify('insert sql:variable("@TableXML") into (/)[1]');

    FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- 输出最终合并后的XML
SELECT @ResultXML AS MergedXML;

关键注意事项 #

• 表名安全处理：必须用QUOTENAME()包裹表名，防止SQL注入，同时兼容带特殊字符的表名。
• 动态SQL参数化：使用sp_executesql并通过输出参数获取XML结果，比直接拼接更安全可靠。
• XML追加方式：使用XML.modify('insert ...')方法实现追加，能保证XML结构的完整性，避免直接字符串拼接可能导致的格式错误。
• 表结构一致性：确保所有目标表都包含id、name字段，且对应的{TableName}_locale表包含FK_Id、FK_Locale、name字段，否则动态SQL会执行报错。

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