Store FOR XML result in xml variable (using WITH statement)

Question

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.)

Mikael Eriksson · Accepted Answer · 2013-07-19 08:24:05Z

9

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')
       );

edited Jul 19, 2013 at 8:24

answered Jul 19, 2013 at 7:56

Mikael Eriksson

22.3k5 gold badges63 silver badges106 bronze badges

Please notice that he's using a table variable. He needs to insert into his table this aux @x variable :-).

Marian
– Marian

2013-07-19 07:58:50 +00:00
Commented Jul 19, 2013 at 7:58
@Mikael Eriksson Thanks Mikael, great answer!

DotNetDeveloper
– DotNetDeveloper

2013-07-19 08:53:34 +00:00
Commented Jul 19, 2013 at 8:53

Add a comment |

Stack Exchange Network

Store FOR XML result in xml variable (using WITH statement)

1 Answer 1

Your Answer

Hot Network Questions

Store FOR XML result in xml variable (using WITH statement)

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions