12

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()?

Control Freak
  • 12,965
  • 30
  • 94
  • 145

1 Answers1

26

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

WorkSmarter
  • 3,738
  • 3
  • 29
  • 34
Gaspa79
  • 5,488
  • 4
  • 40
  • 63
  • What are the aliases `I` and `J` for? – alzaimar Oct 07 '13 at 05:53
  • 2
    I 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. – Gaspa79 Oct 08 '13 at 15:43
  • how, why, where, when, what? (is this documented anywhere? obviously not on MSDN) - is it version specific? – Cee McSharpface Jul 01 '19 at 14:16