Changing hard coded path to variable in MS SQL

问题:

Im using the following code and all works correctly

DECLARE @Directory varchar(100)
SELECT @Directory = 'c:\XML\'

DECLARE @FileExist int
DECLARE @FileName varchar(500),@DeleteCommand varchar(1000),@FullFileName varchar(500), @SQLFullFileName varchar(500)


DECLARE @X XML
SELECT @X = CONVERT(xml,[ICECAT-interface],2)
FROM OPENROWSET(BULK 'C:\XML\1382.xml',SINGLE_BLOB) AS Import([ICECAT-interface])

select P1.X.value('@ID', 'int') as ProductID,
       P2.X.value('@ID', 'int') as ProductID


 from @X.nodes('/ICECAT-interface/Product') as P1(X)
 cross apply P1.X.nodes('ProductRelated') as PR(X)
 cross apply PR.X.nodes('Product') as P2(X)

if i replace the line with the filename in C:\XML\1382.xml with this line

 SELECT @X = CONVERT(xml,[ICECAT-interface],2) 
 FROM OPENROWSET(BULK ' + @FullFileName + ' ,SINGLE_BLOB) AS Import([ICECAT-interface])

it errors saying the file doesn't exist, but in debug mode i can see the variable @FullFileName exists and is correct.

Any input would be appreciated.

Thanks

John

回答1:

You can't pass a variable to OPENROWSET() so you'll need to use dynamic SQL. This is untested but should give you an idea:

DECLARE @x XML, @sql NVARCHAR(MAX);

SELECT @sql = N'SELECT @X = CONVERT(xml,[ICECAT-interface],2) 
 FROM OPENROWSET(BULK ''' + @FullFileName + ''' ,SINGLE_BLOB)
 ---------------------^^ escaped quotes are important 
 AS Import([ICECAT-interface]);';

EXEC sp_executesql @sql, N'@x XML OUTPUT', @x OUTPUT;

SELECT @x;