代码之家 › 专栏 › 技术社区 › Ardalan Shahgholi

如何在openrowset命令中传递批量路径

openrowset bulk-load tsql sql-server

0

Ardalan Shahgholi · 技术社区 · 6 年前

OPENROWSET(BULK ... , SINGLE_BLOB)

    DECLARE @XML XML

    SELECT 
         @XML = CAST( c1 AS XML)    
    FROM OPENROWSET(BULK '\\ServerA\Test\AA.xml', SINGLE_BLOB) AS T1(c1);

    SELECT @XML

    DECLARE @XML_File_Path NVARCHAR(1000) 

    SET  @XML_File_Path = '\\ServerA\Test\AA.xml'

    DECLARE @XML XML

    SELECT 
            @XML = CAST( c1 AS XML)    
    FROM OPENROWSET(BULK @XML_File_Path, SINGLE_BLOB) AS T1(c1);

    SELECT @XML

但这不起作用。如何在大容量关键字之后有一个变量?

1 回复 | 直到 6 年前

1

progm 6 年前

使用动态SQL:

DECLARE @XMLFilePath NVARCHAR(1000)
DECLARE @SQL NVARCHAR(MAX)

SET  @XMLFilePath = '\\ServerA\Test\AA.xml'

SET @SQL = N'
DECLARE @XML XML
SELECT @XML = CAST( c1 AS XML) FROM OPENROWSET(BULK ''' + @XMLFilePath + ''', SINGLE_BLOB) AS T1(c1);
SELECT @XML'

EXEC(@SQL)