T-SQL, Load XML data into a local variable

Question

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?

Eric · Accepted Answer · 2013-12-16 13:28:40Z

5

Found a solution:

DECLARE @results table (result XML)
DECLARE @sqlstmt NVARCHAR(MAX)

SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''' + @file + ''', SINGLE_CLOB) AS xmlData'

INSERT INTO @results EXEC (@sqlstmt)
SELECT @xml = result FROM @results

answered Dec 16, 2013 at 13:28

Eric

1,3173 gold badges16 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sammybar Over a year ago

It is a problem with this solution.

sammybar Over a year ago

It is a problem with this solution. If the xml file is utf-8 encoded and contains international characters (like Ó) AND the xml file DOES NOT starts with the prolog <?xml version="1.0" encoding="UTF-8"?>, in this case sql server 2005 misinterprets the file encoding and the Ó is interpreted like Ã“. According to standards in such a case the xml should be considered by default utf-8 encoded but the sql 2005 fails to do that.

sammybar Over a year ago

I found the solution: use SINGLE_BLOB instead SINGLE_CLOB. Microsoft says: "We recommend that you import XML data only using the SINGLE_BLOB option, rather than SINGLE_CLOB and SINGLE_NCLOB, because only SINGLE_BLOB supports all Windows encoding conversions." at msdn.microsoft.com/en-us/library/ms190312.aspx

roman · Accepted Answer · 2013-12-18 13:38:01Z

3

you can also use sp_executesql:

declare @stmt nvarchar(max), @xml xml

select @stmt = '
    set @xml = (select * from openrowset(bulk ''' + @file + ''', single_clob) as data)
'

exec dbo.sp_executesql
    @stmt = @stmt,
    @params = '@xml xml output',
    @xml = @xml output

edited Dec 18, 2013 at 13:38

answered Dec 16, 2013 at 14:33

roman

118k30 gold badges205 silver badges209 bronze badges

Collectives™ on Stack Overflow

T-SQL, Load XML data into a local variable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related