6

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

1 Answer 1

8

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')
       );
2
  • Please notice that he's using a table variable. He needs to insert into his table this aux @x variable :-).
    – Marian
    Jul 19, 2013 at 7:58
  • @Mikael Eriksson Thanks Mikael, great answer! Jul 19, 2013 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.