SQL - How to assign a record in a variable

Question

AIM : Convert a resultset to XML and assign the result to a variable

Result Set :

M000017690
324067342
324067349
324067355
324154449

Converted XML :

<Products>
  <Product ProductId="324067342" />
  <Product ProductId="324067349" />
  <Product ProductId="324067355" />
  <Product ProductId="324154449" />
  <Product ProductId="M000017690" />
</Products>

Following is the Query I have used to convert the resultset (above) to the XML :

Select 1 AS Tag,NULL AS Parent,  NULL as [Products!1!ProductId],NULL as [Product!2!ProductId]
From @Temp 
UNION 
SELECT 2 AS Tag, 1 AS Parent, ProductId, ProductId
 FROM @Temp
FOR XML EXPLICIT

Now I need to pass this XML to another Stored Proc which expects a parameter type : XML So the basic idea is to create an XML type object :

DECLARE @xml_data as XML

and assign the result of the query to this variable, how do I assign the result of the query to the @xml_data?

Answer 1

How about this (works on SQL Server 2005 and up):

DECLARE @xmldata XML

SELECT @xmlData = 
        (SELECT ProductId as '@ProductId'
         FROM @Temp 
         FOR XML PATH('Product'), ROOT('Products')
        )

SELECT @xmldata

Marc