Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

1 Answer

up vote 2 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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