Union-All and For-XML clause

[Han]

Hello

I am having difficulties using for-xml clause with union-all. e.g.,

select
case
when 1=1 then (
select 1
union all
select 2
for xml path
)
end

I want <row>1</row><row>2</row>, but meet an error.

Msg 1086, Level 15, State 1, Line 9
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 can't understand the message. How do you "wrap the SELECT ..." ?

I have no problem when there is no case-when block. i.e.,

select 1
union all
select 2
for xml path

Can anyone show me the way "wrap the SELECT ..." ?

-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com

[Uri Dimant]

Han
select * from

(

select 1 as a

union all

select 2

) as d for xml path

"Han" <hp4...@kornet.net.korea> wrote in message
news:E24B460D-6430-47F3...@microsoft.com...

[Han]

Thanks Uri Dimant

It works.

select
case when 1=1 then
(

select * from
(
select 1 as a
union all
select 2
)

as d for xml path, type
)
end

"Uri Dimant" <ur...@iscar.co.il> 占쏙옙占쏙옙 占쏙옙占쏙옙 占쌨쏙옙占쏙옙占쏙옙 占쌜쇽옙占쌩쏙옙占싹댐옙.
news:eiFQOHW%23JHA...@TK2MSFTNGP05.phx.gbl...