提问人：VBStarr 提问时间：10/6/2023 最后编辑：Thom AVBStarr 更新时间：10/10/2023 访问量：115
想要使用 OPENROWSET 在 SQL Server 中打开本地 XML 文件
Want to use OPENROWSET to open Local XML file in SQL Server
问：
我正在 SSMS 中使用本地磁盘（在我的 C：\Temp 文件夹中）上的各种 XML 文件进行一些测试，但我已连接到远程测试 SQL Server。有没有办法做这样的事情：
DECLARE @xml xml
-- Want to read from my local, but this attempts to read from the SQL Server's C drive
SELECT @xml = CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK 'C:\Temp\Test.xml', SINGLE_BLOB) AS x;
-- Insert into table on the remote SQL Server
INSERT INTO dbo.MyTable (StatementType, OperatingDate, StatementAmount)
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
FROM @xml.nodes('/Level1/Level2') doc(col)
我一直在寻找这个问题的答案，但没有找到。是的，我知道我可以在我的计算机上本地创建测试 SQL Server，不，我无权将测试 XML 文件复制到 SQL Server 计算机上。但实际上，我只是想知道是否有任何方法可以做到我所要求的。
答：
0赞
DIH
10/6/2023
#1
仅当您处于受保护的网络中时，才尝试此操作。 在您的计算机上共享一个文件夹并将您的文件放在那里。 向所有人授予对此文件夹的访问权限。
在查询中，瞄准您的电脑\yourpc\folderwithonlyonefile\file.xml
呜呜！！
评论
0赞
VBStarr
10/6/2023
不幸的是，我不在受保护的网络上。
0赞
DIH
10/7/2023
所以，避免它。暴露你的份额可能会发生一切。没有其他方法可以在您和服务器之间交换“文件”。您无法访问它的文件系统，也无法访问您的文件系统。
1赞
Charlieface
10/6/2023
#2
SQL Server 只能从它有权访问的驱动器进行大容量加载。除非您以某种方式将客户端的驱动器连接到服务器（可能是一个坏主意），否则它将无法正常工作。
相反，可以使用 Powershell 通过参数化查询发送数据。 不能很好地使用参数，您应该改用 DbaToolsInvoke-SqlCmdInvoke-DbaQuery
$xml = Get-Content -Path "C:\SomePath" -Raw;
$xmlParam = New-DbaSqlParameter -ParameterName "@xml" -SqlDbType Xml -SqlValue $xml;
Invoke-DbaQuery `
-SqlInstance ".\SQLEXPRESS" `
-SqlParameter $xml `
-Query @"
INSERT INTO dbo.MyTable
(StatementType, OperatingDate, StatementAmount)
SELECT
doc.col.value('(StatementType /text())[1]', 'nvarchar(100)'),
doc.col.value('(OperatingDate /text())[1]', 'nvarchar(100)'),
doc.col.value('(StatementAmount/text())[1]', 'numeric(12,2)')
FROM @xml.nodes('/Level1/Level2') doc(col);
"@;
评论
0赞
VBStarr
10/6/2023
是的，@Charlieface，这类似于我想要的，但它不是在 SSMS 中完成的。不过，它给了我一个想法，解决了我的问题。请看下面的回答。
0赞
VBStarr
10/6/2023
#3
由于如前所述，这只是在我的本地计算机上进行测试，因此我访问本地文件系统没有问题。因此，这是一个对我来说效果很好的解决方案。
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXECUTE sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO
DROP TABLE IF EXISTS #xmlfile;
CREATE TABLE #xmlfile ([xmldata] NVARCHAR(max));
DECLARE @Cmd NVARCHAR(50) = 'TYPE "C:\temp\test.xml"';
DECLARE @xmlstring NVARCHAR(MAX);
DECLARE @xml XML;
-- Insert the file's data into the temp table
INSERT INTO #xmlfile EXEC master.dbo.xp_cmdshell @Cmd;
SELECT @xmlstring = COALESCE(@xmlstring, '') + xmldata FROM #xmlfile -- get all rows into a string
SET @xml = CONVERT(xml, @xmlstring); -- convert string to xml and store in variable
-- Output @xml to test
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
FROM @xml.nodes('/Level1/Level2') doc(col)
更新未按预期工作。在我测试的窗口中，我不小心附加到我的本地数据库。相反，我想进入附加到我的远程数据库的窗口。
评论
0赞
Charlieface
10/7/2023
如果文件与 SQL Server 不在同一台计算机上，这也不起作用，因此在这方面它并不比原始文件好（就安全性而言，您必须启用 shell 访问）。
0赞
VBStarr
10/10/2023
@Charlieface - 是的，你是对的。请参阅上面的更新。
评论