Error - The FOR XML clause is invalid in views, inline functions... 1

[PGO01]

I am getting an error with the following code. Can anyone help?

Error message:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

DECLARE @StrXML NVARCHAR(MAX)

SET @StrXML = 
(
	SELECT
	(
		SELECT 'This', 'is', 'a', 'test 1'
		UNION
		SELECT 'This', 'is', 'a', 'test 2'
	)
	FOR XML PATH('Test'), ELEMENTS, ROOT('MyRoot')
)

If I change the code to this I get another error message.

Error Message:
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.

DECLARE @StrXML NVARCHAR(MAX)

SET @StrXML = 
(
	SELECT 'This', 'is', 'a', 'test 1'
	UNION
	SELECT 'This', 'is', 'a', 'test 2'

	FOR XML PATH('Test'), ELEMENTS, ROOT('MyRoot')
)

 

[hmckillop]

try this

DECLARE @StrXML NVARCHAR(MAX)

SELECT @StrXML = 
(
    SELECT * from
    (
        SELECT 'This' as col1, 'is' as col2, 'a' as col3, 'test 1' as col4
        UNION
        SELECT 'This' as col1, 'is' as col2, 'a' as col3, 'test 2' as col4
    ) y
    FOR XML PATH('Test'), ELEMENTS, ROOT('MyRoot')
)

select @StrXML

You need to have the correct syntax for select and also name the columns - though your field names are probably ok.

"I'm living so far beyond my income that we may almost be said to be living apart