T-SQL, Load XML data into a local variable

Viewed 11262

I would like to know, how can I load the XML content from an arbitrary file into a local variable?

This works for a fixed file:

DECLARE @xml XML
SET @xml = 
(
  SELECT *
  FROM OPENROWSET(BULK 'C:\data.xml', SINGLE_BLOB) AS data
)

However, I would like to load the data from any arbitrary file.

This does not work (as BULK seems to only support String arguments)

DECLARE @file NVARCHAR(MAX) = 'C:\data.xml'
DECLARE @xml XML
SET @xml = 
(
  SELECT *
  FROM OPENROWSET(BULK @file, SINGLE_BLOB) AS data
)

I've also tried the following (without success, as the local variable (@xml) seems to be out of scope when the EXEC is performed):

DECLARE @file NVARCHAR(MAX) = 'C:\data.xml'
DECLARE @xml XML
DECLARE @bulk NVARCHAR(MAX) = 'SET @xml = (SELECT * FROM OPENROWSET(BULK ''' + @file + ''', SINGLE_BLOB) AS data)'
EXEC (@bulk)

I'm guessing I need to use a temporary table, but how?

2 Answers
Related