在MS SQL中将硬编码路径更改为变量 [英] 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)

如果我使用此行替换C：\ XML \ 1382.xml中文件名的行

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])

错误说该文件不存在，但在调试模式下我可以看到变量@FullFileName存在并且是正确的。

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

任何输入将不胜感激。

谢谢

约翰

推荐答案

您无法将变量传递给 OPENROWSET（），因此您需要使用动态SQL。这是未经测试但应该给你一个想法：

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;

这篇关于在MS SQL中将硬编码路径更改为变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！