Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

saving the FOR XML AUTO results to variable in SQL

Tags:

select @[email protected]('*')
for xml raw,type  

Above statement will generate following alert:
Msg 6819, Level 16, State 3, Line 2
The FOR XML clause is not allowed in a ASSIGNMENT statement.

like image 613
petergo Avatar asked May 27 '09 04:05

petergo


2 Answers

For example

DECLARE @xml_var XML  
SET @xml_var =  
(
  SELECT   *,
    (  
      SELECT *

      FROM Orders

      WHERE Orders.CustomerID=Customers.CustomerID

      FOR XML AUTO, TYPE

    )

  FROM Customers WHERE CustomerID='ALFKI'

  FOR XML AUTO, TYPE

)

refer to : http://blogs.msdn.com/sqlprogrammability/articles/576095.aspx

like image 189
sesame Avatar answered Sep 23 '22 21:09

sesame


DECLARE @RESULTS XML
SET @RESULTS = (SELECT * FROM Table_Name FOR XML AUTO)
SELECT @RESULTS
like image 33
ViPuL5 Avatar answered Sep 24 '22 21:09

ViPuL5