This query works fine
SELECT 1 AS TAG, NULL AS parent, 1 [dog!1]
UNION (SELECT 1,NULL, 3)
FOR XML EXPLICIT, ROOT('dogs')
I would like to assign it to a variable. This works fine without the union:
DECLARE @x xml
SET @x = ((
(SELECT 1 AS TAG, NULL AS parent, 1 [dog!1]
FOR XML EXPLICIT, ROOT('dogs'))
))
As soon as I introduce the union it doesn't work:
DECLARE @x xml
SET @x = ((
(SELECT 1 AS TAG, NULL AS parent, 1 [dog!1]
UNION (SELECT 1,NULL, 3)
FOR XML EXPLICIT, ROOT('dogs'))
))
Tried all sorts of things. What am I missing
You just need to add one more sub-query level like this:
DECLARE @X XML =
(
SELECT * FROM
(
SELECT 1 AS TAG, NULL AS parent, 1 [dog!1]
UNION
SELECT 1,NULL, 3
)X
FOR XML EXPLICIT, ROOT('dogs')
);
SELECT @X;