I need to create a stored procedure where a SQL statement retrieves data into a variable.

MYStoredProc
(
)
AS
1. Create Var myXML
2. myXML = SQL Statement From JOINS in MyDatabase.MyTables FOR XML....
3. Insert INTO MyDifferentDatabase.MyTransferTable (myIntendedField) VALUES (myXML)

How do I capture the result of the SQL into a variable? Better yet if I can perform 2 and 3 at once how do I do that?

Sample Code Please. Thanks.