Using variables in select and xml files

Copper Contributor

Hi

 

I have created a t-sql that read an xml file and store it into ms sql 2016 database.

 

DECLARE
@x xml,
@@XML_Location VARCHAR(255) = 'FROM OPENROWSET (BULK ''''\\xmlsrv\proxml\'
@XML_File VARCHAR(255) = 'GEN.xml',
;

 

SELECT @x = P
--FROM OPENROWSET (BULK '\\xmlsrv\proxml\GEN.xml', SINGLE_BLOB) AS Element(P)
@XML_Location & @XML_File, SINGLE_BLOB) AS Element(P)

.......

....

 

The script works if i use the from statement directly, but if I try to use declared variables it fails with message:

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@XML_Location '.

 

What am I doing wrong?

 

BR

Dan-Ketil

0 Replies