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