Using UNION ALL in STUFF / XML Path
16,719
There's a simple workaround for that, you should wrap your union query(or any derived table for that matter) with another select. Do this and then continue the syntax normally:
select * from
(
SELECT 1 as I
UNION ALL
SELECT 2 as J
) as K
Something like this is what you're searching 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 it works flawlessly
![Control Freak](https://i.stack.imgur.com/AbdtK.jpg?s=256&g=1)
Comments
-
Control Freak about 2 years
Msg 1086, Level 15, State 1, Line 20 The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
I get this error when i run this:
SELECT STUFF(( SELECT 1 UNION ALL SELECT 2 FOR XML PATH('') ),1,0,'') [COLUMN]
works fine when i run this (without Union ALL)
SELECT STUFF(( SELECT 1 FOR XML PATH('') ),1,0,'') [COLUMN]
Any suggestions why UNION ALL Doesn't work, or how to get it to work inside the
STUFF()
? -
alzaimar over 10 yearsWhat are the aliases
I
andJ
for? -
Gaspa79 over 10 yearsI believe they're not needed, I put them just in case. If you put them you can refer to table dbo.Table1 as j in future references. However, the k IS NEEDED because if it weren't there, there'd be no way to refer to the columns of that subquery.
-
Cee McSharpface about 5 yearshow, why, where, when, what? (is this documented anywhere? obviously not on MSDN) - is it version specific?