如何修改SQL查询以导入C:\Files文件夹下所有XML文件至SQL Server

原SQL仅能读取指定的单个XML文件，要实现读取C:\Files下所有XML文件并导入Projet表，需要先获取文件夹内所有XML文件的路径，再遍历执行导入操作，具体修改如下：

步骤1：准备文件路径列表 #

首先创建临时表存储所有XML文件的完整路径，通过xp_dirtree系统存储过程获取目标文件夹内的XML文件：

-- 创建临时表存储XML文件路径
CREATE TABLE #XmlFiles (FilePath NVARCHAR(255))

-- 从C:\Files文件夹读取所有XML文件的完整路径
INSERT INTO #XmlFiles (FilePath)
SELECT CONCAT('C:\Files\', name) AS FilePath
FROM master.dbo.xp_dirtree('C:\Files', 1, 1)
WHERE name LIKE '%.xml'

步骤2：遍历文件执行导入 #

使用游标遍历临时表中的每个文件路径，动态生成导入SQL并执行：

-- 声明变量存储文件路径和动态SQL
DECLARE @FilePath NVARCHAR(255), @SQL NVARCHAR(MAX)

-- 创建游标遍历所有XML文件
DECLARE XmlFileCursor CURSOR FOR
SELECT FilePath FROM #XmlFiles

OPEN XmlFileCursor
FETCH NEXT FROM XmlFileCursor INTO @FilePath

-- 循环处理每个文件
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 构建插入语句，转义路径中的单引号避免语法错误
    SET @SQL = N'
    INSERT INTO Projet (UID, nom, prenom, matricule, lien)
    SELECT
        page.value(''(item[@name="UID(Zonal OCR)"]/@value)[1]'', ''INT'') AS UID,
        page.value(''(item[@name="nom(Zonal OCR)"]/@value)[1]'', ''VARCHAR(50)'') AS nom,
        page.value(''(item[@name="prenom(Zonal OCR)"]/@value)[1]'', ''VARCHAR(50)'') AS prenom,
        page.value(''(item[@name="matricule(Zonal OCR)"]/@value)[1]'', ''VARCHAR(50)'') AS matricule,
        page.value(''(item[@name="lien"]/@value)[1]'', ''VARCHAR(100)'') AS lien 
    FROM 
        (SELECT 
             CAST(MY_XML AS xml)
         FROM 
             OPENROWSET(BULK ''' + REPLACE(@FilePath, '''', '''''') + ''', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
    CROSS APPLY 
        MY_XML.nodes(''root/page'') AS MY_XML (page);'

    -- 执行动态SQL
    EXEC sp_executesql @SQL

    FETCH NEXT FROM XmlFileCursor INTO @FilePath
END

-- 清理游标和临时表
CLOSE XmlFileCursor
DEALLOCATE XmlFileCursor
DROP TABLE #XmlFiles

注意事项 #

• 确保SQL Server服务账号拥有C:\Files文件夹的读取权限，否则无法访问文件
• xp_dirtree需要在master数据库中执行，且当前账号需具备该存储过程的执行权限
• 若文件路径包含单引号，REPLACE(@FilePath, '''', '''''')会自动转义，避免SQL语法错误
• 可通过调整WHERE name LIKE '%.xml'的筛选条件，控制需要导入的XML文件范围

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