Using UNION ALL in a STUFF / XML Path

Question

Msg 1086 Level 15 State 1 Line 20 The FOR XML clause is not valid in views, built-in functions, views, and subqueries when they contain a dial statement. To get around, wrap the SELECT containing the set statement using the view syntax and apply FOR XML on top of it.

I get this error when I run it:

SELECT STUFF(( SELECT 1 UNION ALL SELECT 2 FOR XML PATH('') ),1,0,'') [COLUMN] 

works fine when I run this (without ALL ALL)

 SELECT STUFF(( SELECT 1 FOR XML PATH('') ),1,0,'') [COLUMN] 

Any suggestions why UNION ALL is not working or how to make it work inside STUFF() ?

Answer 1

There is a simple workaround for this; you must combine your join query (or any view) with another choice. Do this and then continue with the syntax normally:

 select * from ( SELECT 1 as I UNION ALL SELECT 2 as J ) as K 

Something like this you are looking for:

 SELECT STUFF(( select * from( SELECT * from dbo.Table1 as I UNION ALL SELECT * from dbo.Table2 as j ) as k FOR XML PATH('') ),1,0,'') 

I checked and works flawlessly