CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
rakamakafo
rakamakafo

Reputation: 1154

Saving Dynamic query result into variable

I have a variable (@a) with query:

    select Field1 as [TD],Field2 as [TD]
from MLSFeed 
For XML raw('tr'), Elements

How to save xml result (dynamic query) into another variable (@b)?

I tried to create Temporary table and insert there result of DynamicSQL, but that doesn't allow to enter xml result into table.

Upvotes: 1

Views: 1041

Answers (2)

Deadsheep39
Deadsheep39

Reputation: 611

Declare variable and fill it. Without dynamical qry.

declare @a xml
set @a = (
    select Field1 as [TD],Field2 as [TD]
    from MLSFeed
    for xml raw('tr'), elements)

--select @a

declare @b xml -- you asked for @b
set @b = @a

You shouldn't use dynamical qry without reason.

Upvotes: 0

Searching
Searching

Reputation: 2279

Try this 

DECLARE @MyOutput NVARCHAR(max) = '';
DECLARE @MyQuery NVARCHAR(max) = 'SELECT @MyOutput = (SELECT s.name FROM sys.databases s FOR XML AUTO)'
DECLARE @ParmDefinition NVARCHAR(500);

SET @ParmDefinition = N'@MyOutput nvarchar(max) OUTPUT';

EXECUTE sp_executesql @MyQuery
    ,@ParmDefinition
    ,@MyOutput = @MyOutput OUTPUT;

SELECT @MyOutput

You will need to replace the variables and tables as required. Let us know.

Upvotes: 2

Related Questions