XML文件如何导入SQL Server 使用OPENROWSET批量导入数据

openrowset读取xml需启用ad hoc distributed queries并用bulk single_blob加载，路径须为sql server服务账户可访问的本地绝对路径，大文件易内存溢出，应拆分或改用流式处理。

OPENROWSET 读取 XML 文件必须启用 Ad Hoc Distributed Queries

SQL Server 默认禁用 OPENROWSET 的非 SQL Server 数据源访问，XML 导入属于这一类。不开启会直接报错：Msg 7415, Level 12, State 1: Ad hoc access to OLE DB provider 'MSDASQL' has been denied。

执行前必须运行：

EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

注意：该配置需 sysadmin 权限；生产环境开启后建议评估安全风险，导入完可考虑关掉。

XML 文件路径必须是 SQL Server 进程可访问的本地路径

OPENROWSET 中的文件路径不是你本机的路径，而是 SQL Server 服务账户（如 NT ServiceMSSQLSERVER）能读到的位置。网络路径（servershare）或带盘符的绝对路径（C:dataile.xml）都可能失败。

• 最稳妥方式：把 XML 放在 SQL Server 所在机器的本地目录（如 D:importinput.xml），并确保服务账户对该目录有读取权限
• 不能用相对路径或用户桌面路径（如 C:UsersAdminDesktop...）
• 如果用 UNC 路径，需给 SQL Server 服务账户显式授予共享+NTFS 读取权限，实操中容易漏掉 NTFS 层

OPENROWSET + BULK + XML 需指定 SINGLE_BLOB 和正确的数据类型

XML 不是文本行格式，不能当普通 CSV 用 BULK INSERT。必须用 OPENROWSET 的 BULK 选项加载为二进制，再用 .nodes() 解析。

常见错误写法：select * FROM OPENROWSET(...) 直接查——这会报错，因为没指定解析逻辑。

正确结构要点：

• 必须用 BULK 'D:importinput.xml' WITH (SINGLE_BLOB) 加载原始字节
• 外层要套一层 SELECT CAST(x AS XML) 转成 XML 类型
• 再用 .nodes('/root/item') 定位节点，配合 .value() 提取字段

示例片段：

SELECT    T.c.value('(name/text())[1]', 'NVARCHAR(100)') AS name,   T.c.value('(age/text())[1]', 'INT') AS age FROM (   SELECT CAST(BulkColumn AS XML) AS x    FROM OPENROWSET(BULK 'D:importinput.xml', SINGLE_BLOB) AS t ) AS x CROSS APPLY x.x.nodes('/users/user') AS T(c);

大 XML 文件容易内存溢出或超时，别硬扛

OPENROWSET 把整个 XML 一次性读进内存再转 XML 类型，文件超过 10MB 就可能触发 XML parsing: unable to switch encoding 或直接超时（默认 600 秒）。

这时不是调大 timeout 就能解决：

• SQL Server 对单次 XML 实例大小有限制（取决于版本，通常上限在 50–100MB）
• 更稳的方式是拆分 XML：用 PowerShell 或 Python 先按 <item></item> 拆成小文件，再循环导入
• 或者改用 SqlBulkCopy + .NET 程序解析流式读取，绕过 SQL Server 的 XML 内存瓶颈

真正卡住的时候，往往不是语法错，而是服务器资源耗尽后静默失败——查不到错误日志，只看到查询一直 running。