Sql-server – Store FOR XML result in xml variable (using WITH statement)

sql-server-2008xml

I am creating xml with a SQL query that works like this: First a CTE, then the SELECT statement that creates the XML.

That works. However I would like to store the output in a XML variable (a table variable is also ok if that's easier). But I can't seem to get it to work (see second snippet). Any suggestions?

The XML generation that works:

;WITH cte
       AS (SELECT ...
          )
  SELECT ...
  FOR XML PATH ('root')

This was my attempt to store it in a table variable, but I couldn't get it to work:

DECLARE @myXml TABLE(
    x xml
);
;WITH cte
       AS (SELECT ...
          )
  INSERT INTO @myXml SELECT ...
  FOR XML PATH ('root')

This is the resulting error message:

Meldung 6819, Ebene 16, Status 1, Zeile 240
Die FOR XML-Klausel ist in einer INSERT-Anweisung nicht zulässig.

(I can't use FOR XML in an insert statement.)

Best Answer

You put the assignment after the CTE declaration(s). 

declare @X xml;

with C as
(
  select 1 as X, 2 as Y
)
select @X = 
  (
  select *
  from C
  for xml path('root')
  );

If you want the XML to end up in a table variable it would look like this.

declare @T table (X xml);

with C as
(
  select 1 as X, 2 as Y
)
insert into @T(X)
select (
       select *
       from C
       for xml path('root')
       );
Related Question