如何在远程SQL Server中使用OPENROWSET读取本地XML文件？

针对你在SSMS连接远程SQL Server时，无法将本地C:\Temp的XML文件拷贝到远程服务器，却要把XML数据导入远程表的需求，提供以下三种可行方案：

方案一：使用SSMS导入数据向导（图形化操作，适合单/少量文件） #

• 打开SSMS并连接远程SQL Server，右键目标数据库 → 任务 → 导入数据
• 数据源选择「XML文件」，点击「浏览」选择本地C:\Temp下的目标XML文件，按需配置XML解析规则（可让向导自动检测结构）
• 目标选择已连接的远程SQL Server，指定目标表为dbo.MyTable
• 在「列映射」步骤确认StatementType、OperatingDate、StatementAmount字段对应正确
• 点击「完成」执行导入，数据将直接插入远程表

方案二：PowerShell脚本批量处理（适合多XML文件） #

通过PowerShell读取本地XML并直接插入远程SQL Server，脚本示例：

# 加载SQL Server模块（若未安装需先执行 Install-Module SqlServer 安装）
Import-Module SqlServer

# 配置远程SQL Server信息
$remoteServer = "远程服务器名/实例名"
$targetDB = "目标数据库名称"
$targetTable = "dbo.MyTable"

# 遍历C:\Temp下所有XML文件
Get-ChildItem "C:\Temp\*.xml" | ForEach-Object {
    # 读取并解析XML内容
    $xmlDoc = [xml](Get-Content $_.FullName)
    $xmlNodes = $xmlDoc.SelectNodes("/Level1/Level2")

    # 逐个节点插入远程表
    foreach ($node in $xmlNodes) {
        $stmtType = $node.StatementType
        $opDate = $node.OperatingDate
        $stmtAmount = [decimal]$node.StatementAmount

        # 构造插入SQL
        $insertSql = @"
INSERT INTO $targetTable (StatementType, OperatingDate, StatementAmount)
VALUES ('$stmtType', '$opDate', $stmtAmount)
"@

        # 执行SQL到远程服务器
        Invoke-SqlCmd -ServerInstance $remoteServer -Database $targetDB -Query $insertSql
    }
}

方案三：借助本地SQL Server作为中间层（若已搭建本地测试库） #

先在本地SQL Server读取XML，再通过链接服务器将数据推送到远程：

1. 本地SQL Server读取XML并暂存到临时表：

DECLARE @xml xml
SELECT @xml = CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK 'C:\Temp\Test.xml', SINGLE_BLOB) AS x;

SELECT  
    doc.col.value('(StatementType)[1]', 'nvarchar(100)') StatementType,
    doc.col.value('(OperatingDate)[1]', 'nvarchar(100)') OperatingDate,
    doc.col.value('(StatementAmount)[1]', 'numeric(12,2)') StatementAmount
INTO #TempData
FROM @xml.nodes('/Level1/Level2') doc(col)

2. 创建链接服务器指向远程SQL Server（若未创建）：

-- 创建链接服务器（自定义别名RemoteSQL）
EXEC sp_addlinkedserver 
    @server = 'RemoteSQL',
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = '远程服务器名/实例名';

-- 添加登录映射（按需配置远程账号）
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'RemoteSQL',
    @useself = 'FALSE',
    @rmtuser = '远程SQL账号',
    @rmtpassword = '远程账号密码';

3. 将本地临时表数据插入远程表：

INSERT INTO [RemoteSQL].[目标数据库名].dbo.MyTable 
    (StatementType, OperatingDate, StatementAmount)
SELECT StatementType, OperatingDate, StatementAmount FROM #TempData

DROP TABLE #TempData

注意事项 #

• 所有方案都需要确保你拥有：本地XML文件的读取权限、远程SQL Server的登录权限、目标表dbo.MyTable的插入权限
• PowerShell方案需确保本地已安装SqlServer模块
• 链接服务器方案需本地SQL Server具备访问远程SQL Server的网络权限

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