如何将MS SQL中多个SELECT...FOR XML PATH结果合并为单个XML文件

问题描述 #

通过游标遍历INFORMATION_SCHEMA.TABLES获取表名，动态生成每个表的SELECT...FOR XML PATH查询并执行，每个查询输出独立的<value>片段，需要将所有片段合并为一个完整的XML结果，尝试存入XML变量但未成功。

实现方法 #

核心思路是用XML类型变量累加每个动态查询的结果，通过sp_executesql获取每个查询的XML输出并拼接，最终得到合并后的XML。以下是完整可运行的代码示例：

-- 声明存储最终合并结果的XML变量
DECLARE @combinedXml XML = '';
DECLARE @tableName NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE @tempXml XML;
DECLARE @cursorValue INT = 1; -- 替换为你的游标变量实际值

-- 声明游标，按需过滤目标表
DECLARE cursor_c CURSOR FOR
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
  -- 可添加额外过滤条件，如 TABLE_NAME LIKE 'YourTablePrefix%'

OPEN cursor_c;
FETCH NEXT FROM cursor_c INTO @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 动态生成SQL，用QUOTENAME避免表名特殊字符问题
    SET @sql = N'
        SELECT 
            id AS ''value/@id'',
            name AS ''value''
        FROM ' + QUOTENAME(@tableName) + '
        WHERE somevalue = @cursorValue
        FOR XML PATH('''')';

    -- 执行动态SQL，通过输出参数获取当前表的XML片段
    EXEC sp_executesql 
        @stmt = @sql,
        @params = N'@cursorValue INT, @xmlResult XML OUTPUT',
        @cursorValue = @cursorValue,
        @xmlResult = @tempXml OUTPUT;

    -- 将当前XML片段追加到合并变量中
    SET @combinedXml = @combinedXml.query('concat(., sql:variable("@tempXml"))');

    FETCH NEXT FROM cursor_c INTO @tableName;
END

CLOSE cursor_c;
DEALLOCATE cursor_c;

-- 输出合并后的XML（可选包装根节点）
-- 直接输出片段集合
SELECT @combinedXml AS CombinedXml;

-- 或者包装为带根节点的完整XML
SELECT @combinedXml AS '*' FOR XML PATH('root');

关键注意事项 #

• 避免SQL注入与语法错误：用QUOTENAME(@tableName)处理表名，用sp_executesql参数化查询条件@cursorValue，不要直接拼接字符串值。
• 正确拼接XML：使用XML的query方法结合concat函数拼接，不要用VARCHAR类型拼接XML（会导致编码或格式损坏）。
• 获取动态查询结果：必须通过sp_executesql的输出参数捕获XML结果，直接exec(@sql)只会输出独立结果集，无法存入变量。

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