如何使用SSIS或SQL将表中同结构XML合并为单个XML？

嘿，我来帮你搞定这个XML合并的需求！下面分别提供SQL直接处理和SSIS实现两种方案，你可以根据场景选择：

一、SQL Server 直接处理方案 #

这个方案不需要额外工具，直接用T-SQL就能完成，效率也很高：

首先先确认你的测试表（和你提供的一致）：

Declare @YourTable Table ([ColA] int,[ColB] xml) 
Insert Into @YourTable Values 
(123,'<XMLData><ID>1</ID></XMLData>') 
,(456,'<XMLData><ID>2</ID></XMLData>') 
,(333,'<XMLData><ID>3</ID></XMLData>')

然后执行下面的SQL语句，就能得到你想要的合并后XML：

SELECT 
    (SELECT ColB.value('(/XMLData/ID)[1]', 'int') AS ID
     FROM @YourTable
     FOR XML PATH(''), TYPE)
FOR XML PATH('XMLData'), TYPE

解释： #

• 内层的SELECT ColB.value('(/XMLData/ID)[1]', 'int') AS ID FROM @YourTable FOR XML PATH(''), TYPE会把所有行的ID节点提取出来，并且不生成默认的根节点（因为PATH('')）；
• 外层的FOR XML PATH('XMLData')会把这些ID节点包裹到<XMLData>根节点里，最终生成你需要的结构。

执行后得到的结果就是：

<XMLData>
  <ID>1</ID>
  <ID>2</ID>
  <ID>3</ID>
</XMLData>

二、SSIS 实现方案 #

如果需要用SSIS来完成这个任务，可以按照以下步骤操作：

• 新建SSIS包，添加一个数据流任务；
• 在数据流中添加OLE DB源，连接到你的SQL Server数据库，选择目标表（替换成你的真实表）；
• 添加一个脚本组件（作为转换），连接OLE DB源的输出：
  • 在脚本组件的输入列中勾选ColB；
  • 在脚本组件的变量中创建一个字符串类型的变量（比如strXML），用来存储收集到的ID节点；
  • 编辑脚本，在PreExecute方法中初始化strXML为<XMLData>；
  • 在Input0_ProcessInputRow方法中，提取当前行的ID值，拼接到strXML中：

    string id = Row.ColB.SelectSingleNode("/XMLData/ID").InnerText;
    Variables.strXML += $"<ID>{id}</ID>";
    

  • 在PostExecute方法中，给strXML加上闭合标签</XMLData>；
• 添加一个派生列组件或者直接用脚本任务把最终的strXML变量写入到文件、数据库或者其他目标位置；
• 运行包即可得到合并后的XML。

注意： #

如果你的表数据量很大，建议优先用SQL方案，执行效率更高；SSIS方案更适合需要和其他ETL流程整合的场景。

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