在强制转换为XML时使用Distinct

Question

你能在Distinct()上运行XML吗？我之所以这样问，是因为如果您查看这些垃圾数据，它会产生这个错误。

输出 SQLState = 37000，NativeError =402 Error =微软SQL Server添加operator. SQLState = 37000，NativeError =8180 错误=微软SQL Server语句(S)中的数据类型不兼容

如何成功地使用Distinct并强制转换为XML运行bcp语句？

Declare @TestData Table (pinkpurpleblue varchar(max))
Insert Into @TestData (pinkpurpleblue) Values
('xx11'), ('rr22'), ('xx11'), ('rr22')

Declare @file nvarchar(200), @sql nvarchar(max)
Set @file = 'C:\Prod\Results\testing.csv'
Set @sql = 'bcp "Select ''pinkpurpleblue'' UNION ALL SELECT CAST(DISTINCT(pinkpurpleblue) As XML) FROM @TestData''" queryout "'
Set @sql = @Sql + @file + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql 

即使使用这种语法，我也会得到相同的错误，而且(我认为)在本例中，我只是尝试将nvarchar列强制转换为xml。

Declare @TestData Table (pinkpurpleblue varchar(max))
Insert Into @TestData (pinkpurpleblue) Values
('xx11'), ('rr22'), ('xx11'), ('rr22')

Create Table #Test123
(
  pinkpb varchar(max)
)

Insert Into #Test123 (pinkpb)
Select DISTINCT(pinkpurpleblue) FROM @TestData

Declare @file nvarchar(200), @sql nvarchar(max)
Set @file = 'C:\Prod\Results\testing.csv'
Set @sql = 'bcp "Select ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpurpleblue     As XML) FROM #Test123''" queryout "'
Set @sql = @Sql + @file + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql 

Answer 1

你有两个问题我看得出来：

1. 您不能将VARCHAR(MAX)传递给xp_cmdshell，可以传递的最大的是VARCHAR(8000)或NVARCHAR(4000)，根据xp_cmdshell文档这里。
2. 您的临时表(以及表变量)超出了其他连接的范围，因此bcp将无法看到它。

我围绕这些问题开展了以下工作：

DECLARE @TestData TABLE (
    pinkpurpleblue VARCHAR(MAX)
)

INSERT INTO @TestData (pinkpurpleblue) VALUES ('xx11'), ('rr22'), ('xx11'), ('rr22')

CREATE TABLE ##distinctValues (
    pinkpb VARCHAR(MAX)
)

INSERT INTO ##distinctValues (pinkpb) SELECT DISTINCT pinkpurpleblue FROM @TestData

DECLARE @file VARCHAR(200), @sql VARCHAR(8000)
SET @file = 'C:\Prod\Results\testing.csv'
SET @sql = 'bcp "SELECT ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpb AS XML) FROM ##distinctValues" queryout "' 
SET @sql = @sql + @file + '" -c -t, -T -S '+@@SERVERNAME
EXEC MASTER..xp_cmdshell @sql 

DROP TABLE ##distinctValues

在这里，我确保@sql的类型是VARCHAR(8000)而不是VARCHAR(MAX)，我使用的是全局临时表，而不是本地表，这样bcp就可以看到它。