如何用SSIS导入结构不同但含公共节点的XML至SQL Server

嘿，这个场景我太熟悉了——面对一堆结构各异但藏着相同核心节点的XML，没法做统一XSD确实头疼，但SQL Server其实有现成的办法搞定，不用慌！

核心思路就是忽略XML的整体结构，直接通过XPath定位公共节点，不需要依赖XSD来验证或映射。下面给你两种实用的方法：

方法1：XQuery（推荐，简洁高效） #

XQuery是SQL Server处理XML的原生工具，能直接在XML列上操作，不需要额外的文档句柄，代码更简洁。

步骤： #

1. 先把所有XML文件导入到一个临时表（或者永久表）的XML类型列中：

-- 创建临时表存储XML数据
CREATE TABLE #XmlFiles (XmlContent XML);

-- 用BULK INSERT导入单个XML文件（如果是多个文件，可以循环或者用SSIS批量导入）
BULK INSERT #XmlFiles
FROM 'C:\YourFiles\Sample1.xml'
WITH (ROWTERMINATOR = '0x00'); -- 因为XML是单条完整数据，用空字符作为行终止符

2. 直接用XQuery提取公共节点：
   假设你的公共节点是<ID>、<Name>、<CreateDate>，不管它们在XML的哪个层级，用//语法就能全局查找：

-- 假设你的目标表是这样的
CREATE TABLE TargetTable (
    ID INT,
    Name NVARCHAR(100),
    CreateDate DATETIME
);

-- 提取并插入数据
INSERT INTO TargetTable (ID, Name, CreateDate)
SELECT
    -- 用[1]确保只取第一个匹配的节点（如果XML里有多个相同节点，可调整逻辑）
    XmlContent.value('(//ID)[1]', 'INT') AS ID,
    XmlContent.value('(//Name)[1]', 'NVARCHAR(100)') AS Name,
    XmlContent.value('(//CreateDate)[1]', 'DATETIME') AS CreateDate
FROM #XmlFiles
-- 可选：过滤掉缺少公共节点的无效XML
WHERE XmlContent.exist('//ID') = 1 
  AND XmlContent.exist('//Name') = 1;

如果XML里有多个重复的公共节点组（比如一个XML包含多条记录），可以用nodes()方法拆分：

INSERT INTO TargetTable (ID, Name, CreateDate)
SELECT
    Record.value('(ID)[1]', 'INT') AS ID,
    Record.value('(Name)[1]', 'NVARCHAR(100)') AS Name,
    Record.value('(CreateDate)[1]', 'DATETIME') AS CreateDate
FROM #XmlFiles
-- 用nodes()定位到包含公共节点的父元素（如果没有固定父元素，用//*[ID and Name]匹配所有包含这三个节点的元素）
CROSS APPLY XmlContent.nodes('//Customer') AS T(Record);

方法2：OPENXML（兼容旧版本SQL Server） #

如果你用的是较旧的SQL Server版本（比如2008及以前），OPENXML也是个可靠的选择，它需要先解析XML文档生成句柄，再提取数据。

DECLARE @XmlHandle INT;
DECLARE @XmlData XML;

-- 遍历所有XML文件
DECLARE XmlCursor CURSOR FOR SELECT XmlContent FROM #XmlFiles;
OPEN XmlCursor;
FETCH NEXT FROM XmlCursor INTO @XmlData;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 准备XML文档句柄
    EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @XmlData;

    -- 提取公共节点，用//全局定位
    INSERT INTO TargetTable (ID, Name, CreateDate)
    SELECT ID, Name, CreateDate
    FROM OPENXML(@XmlHandle, '//*', 2) -- 2表示属性中心映射，这里用//*匹配所有元素
    WITH (
        ID INT '//ID',
        Name NVARCHAR(100) '//Name',
        CreateDate DATETIME '//CreateDate'
    );

    -- 释放文档句柄，避免内存泄漏
    EXEC sp_xml_removedocument @XmlHandle;

    FETCH NEXT FROM XmlCursor INTO @XmlData;
END

CLOSE XmlCursor;
DEALLOCATE XmlCursor;

额外注意事项 #

• 命名空间处理：如果你的XML带有命名空间，需要先声明命名空间再查询：

WITH XMLNAMESPACES ('http://yourcompany.com/ns' AS ns)
SELECT
    XmlContent.value('(//ns:ID)[1]', 'INT') AS ID
FROM #XmlFiles;

• 批量导入XML：如果有大量XML文件，用SSIS的「XML源」组件会更高效，它可以直接读取文件夹下的所有XML文件，然后通过派生列或XQuery转换提取公共节点。
• 数据验证：如果担心XML里的公共节点格式错误，可以在插入前用TRY_CAST或TRY_CONVERT做容错处理：

TRY_CONVERT(INT, XmlContent.value('(//ID)[1]', 'NVARCHAR(20)')) AS ID

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