Use Distinct With Cast As XML

Question

Can you run Distinct() on XML? I ask because if you look at this garbage data, it produces this error

output
SQLState = 37000, NativeError = 402
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types char and xml are incompatible in the add operator.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL

How can I succesfully run this bcp statement with Distinct and Cast to XML?

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 

Even with this syntax I get the same error, and (I think) I am only attempting to cast a nvarchar column to xml in this example

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

You have 2 issues that I can see:

1. You cannot pass a VARCHAR(MAX) to xp_cmdshell, largest you can pass is VARCHAR(8000) or NVARCHAR(4000), as per the xp_cmdshell documentation here.
2. Your temp table (and your Table variable for that matter) are out of scope for other connections, so bcp won't be able to see it.

I worked around these issues with the following:

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

Here, I make sure that the type of @sql is VARCHAR(8000) rather than VARCHAR(MAX) and I use a global temp table instead of a local one, so that bcp will be able to see it.