Search code examples
sql-server-2008for-xml-explicit

Assigning results for a FOR XML EXPLICIT UNION to a variable


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


Solution

  • 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;