sql-server - 从 sql server 中的 XML 文件中提取 XML 数据时出错

DECLARE @TablesList TABLE
(
    TableName VARCHAR(500),
    RefTable VARCHAR(500),
    RefTableIDColumn VARCHAR(500)
)
DECLARE @Query AS VARCHAR(MAX)


SET @Query = 'DECLARE @badIds AS VARCHAR(500) DECLARE @TableXML AS XML'
SET @Query = @Query + ' SELECT @TableXML = xCol FROM (SELECT * FROM OPENROWSET (BULK ''\\10.0.0.60\Temp\path\DataItemTables.xml'', SINGLE_CLOB)AS xCol) AS R(xCol)'
SET @Query = @Query + ' INSERT INTO @TablesList SELECT ref.value(''tablename[1]'',''nvarchar(500)'') AS tablename,'
SET @Query = @Query + ' ref.value(''refTable[1]'',''nvarchar(500)'') AS refTable, ref.value(''refTableIDColumn[1]'',''nvarchar(500)'') AS refTableIDColumn FROM'
SET @Query = @Query + ' @TableXML.nodes(''//Table[@name="Description"]'') AS R(ref)'
SET @Query = @Query +'select * from @TablesList'

EXEC(@Query)

我正在执行上面的脚本。但我收到如下错误

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TablesList".
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TablesList".

我做错了什么。但是当我以如下动态形式编写查询时，它工作正常。问题是我想删除 SP 的所有动态部分。

DECLARE @Query AS VARCHAR(MAX)

SET @Query ='DECLARE @TablesList TABLE ( TableName VARCHAR(500),RefTable VARCHAR(500),RefTableIDColumn VARCHAR(500))'
SET @Query = @Query + ' DECLARE @badIds AS VARCHAR(500) DECLARE @TableXML AS XML'
SET @Query = @Query + ' SELECT @TableXML = xCol FROM (SELECT * FROM OPENROWSET (BULK ''\\10.0.0.60\Temp\Path\DataItemTables.xml'', SINGLE_CLOB)AS xCol) AS R(xCol)'
SET @Query = @Query + ' INSERT INTO @TablesList SELECT ref.value(''tablename[1]'',''nvarchar(500)'') AS tablename,'
SET @Query = @Query + ' ref.value(''refTable[1]'',''nvarchar(500)'') AS refTable, ref.value(''refTableIDColumn[1]'',''nvarchar(500)'') AS refTableIDColumn FROM'
SET @Query = @Query + ' @TableXML.nodes(''//Table[@name="Description"]'') AS R(ref)'
SET @Query = @Query +'select * from @TablesList'

EXEC(@Query)

最佳答案

您只能在声明它的同一范围内访问表变量。由于您的 EXEC 位于不同的范围内，因此无法识别表变量。解决此问题的一种方法是改用临时表:

CREATE TABLE #TablesList
(
    TableName VARCHAR(500),
    RefTable VARCHAR(500),
    RefTableIDColumn VARCHAR(500)
)
DECLARE @Query AS VARCHAR(MAX)

SET @Query = 'DECLARE @badIds AS VARCHAR(500) DECLARE @TableXML AS XML'
SET @Query = @Query + ' SELECT @TableXML = xCol FROM (SELECT * FROM OPENROWSET (BULK ''\\10.0.0.60\Temp\path\DataItemTables.xml'', SINGLE_CLOB)AS xCol) AS R(xCol)'
SET @Query = @Query + ' INSERT INTO #TablesList SELECT ref.value(''tablename[1]'',''nvarchar(500)'') AS tablename,'
SET @Query = @Query + ' ref.value(''refTable[1]'',''nvarchar(500)'') AS refTable, ref.value(''refTableIDColumn[1]'',''nvarchar(500)'') AS refTableIDColumn FROM'
SET @Query = @Query + ' @TableXML.nodes(''//Table[@name="Description"]'') AS R(ref)'
SET @Query = @Query +'select * from #TablesList'

EXEC(@Query)

关于sql-server - 从 sql server 中的 XML 文件中提取 XML 数据时出错，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36930430/