Want to use OPENROWSET to open Local XML file in SQL Server

Question

I'm doing some testing in SSMS with various XML files on my LOCAL disk (in my C:\Temp folder), but I am connected to a remote test SQL Server. Is there any way to do something like this:

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)

I've searched for the answer to this, but have not found it. And yes, I know I could create a test SQL Server locally on my machine, and no, I don't have access to copy the test XML files onto the SQL Server machine. But really, I just want to know if there's any way to do what I am asking.

Answer 1

Only if u are in a protected network try this. Share a folder on your computer and put there your file. Grant access to this folder to everyone.

In the query aim to your pc \yourpc\folderwithonlyonefile\file.xml

Lmk!!

Answer 2

SQL Server can only bulk load from a drive it has access to. Unless you somehow connect the client's drive to the server (probably a bad idea) it won't work.

Instead, you can use Powershell to send the data via a parameterized query. Invoke-SqlCmd doesn't work well with parameters, you should use the DbaTools Invoke-DbaQuery instead

$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);
"@;

Answer 3

Since this is only testing on my local machine as mentioned, I have no problem accessing the local file system. Thus, this is a solution that worked fine for me.

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)

UPDATE Not working as expected. I was accidently attached to my local DB in the window I tested this. Instead, I want to be in a window attached to my remote DB.