NVARCHAR 在 SQL Server 中被视为 XML

Question

我编写了这个查询来将所有列连接在一起，但它总是抛出此错误： 查询：

DECLARE @SCHEMA VARCHAR(25) = 'PERSON'
DECLARE @TABLE_NAME VARCHAR(25) = 'PERSON'
DECLARE @I INT =1
DROP TABLE IF EXISTS #COLUMNS
SELECT
ORDINAL_POSITION,
COLUMN_NAME
INTO #COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
DECLARE @NUMBER_OF_COLUMN INT = (SELECT COUNT(*) FROM #COLUMNS)
DECLARE @COLUMNLIST NVARCHAR(MAX)
WHILE @I<@NUMBER_OF_COLUMN
BEGIN
SET @COLUMNLIST = ISNULL(@COLUMNLIST,
(SELECT COLUMN_NAME
FROM #COLUMNS
WHERE ORDINAL_POSITION = @I
)) + ', ' +
(SELECT COLUMN_NAME
FROM #COLUMNS
WHERE ORDINAL_POSITION = @I)
SET @I = @I + 1
END
DECLARE @QUERY NVARCHAR(MAX) = 'SELECT CONCAT('+@COLUMNLIST+') FROM Person.Person'
EXEC (@QUERY)

错误：

不允许从数据类型 xml 到 nvarchar 的隐式转换。使用 CONVERT 函数运行此查询。

错误似乎来自以下代码行：DECLARE @QUERY NVARCHAR(MAX) = 'SELECT CONCAT('+@COLUMNLIST+') FROM Person.Person'

我该如何解决？

Answer 1

DECLARE @SCHEMA VARCHAR(25) = 'PERSON';
DECLARE @TABLE_NAME VARCHAR(25) = 'PERSON';
DECLARE @columnlist varchar(max);
SELECT @columnlist = string_agg(COLUMN_NAME, ', ')
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
TABLE_SCHEMA = @SCHEMA 
AND TABLE_NAME = @TABLE_NAME;
SELECT @columnlist;

Answer 2

很可能您收到错误是因为该表以前包含 XML。

您根本不需要临时表。你可以只使用STRING_AGG.

• 您还应该使用QUOTENAME引用名称。
• 对象名称应位于声明为sysname的变量中，这是nvarchar(128)的别名
• 您应该使用还允许您传递参数的sp_executesql。
• INFORMATION_SCHEMA.COLUMNS仅用于兼容性。请改用sys.columns。

DECLARE @SCHEMA sysname = 'Person';
DECLARE @TABLE_NAME sysname = 'Person';
DECLARE @sql nvarchar(max) = '
SELECT CONCAT(' + (
SELECT STRING_AGG('CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max))', ',
') + ')'
FROM sys.columns c
JOIN sys.tables t ON t.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = @TABLE_NAME
AND s.name = @SCHEMA
) + '
FROM ' + QUOTENAME(@SCHEMA) + '.' + QUOTENAME(@TABLE_NAME) + ';';
EXEC sp_executesql @sql;  -- can also pass parameters

Answer 3

在Mitch Wheat的基础上，您首先需要将所有列转换为文本。使用 Cast(col as varchar(max))

即你能用

DECLARE @SCHEMA VARCHAR(25) = 'PERSON';
DECLARE @TABLE_NAME VARCHAR(25) = 'PERSON';
DECLARE @columnlist varchar(max);
Select  @columnlist = string_agg(ColAsString, ', ') from (
select 'CAST(' + COLUMN_NAME + ' as nvarchar(max))' as ColAsString
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
TABLE_SCHEMA = @SCHEMA 
AND TABLE_NAME = @TABLE_NAME) as Data
--SELECT @columnlist;
Declare @SQL as NVARCHAR(max)
Select  @SQL = 'Select CONCAT(' + @columnlist + ' ) as ALLINONE from ' + @SCHEMA + '.' + @TABLE_NAME
Exec(@SQL)