CODEWITHSUNDEEP
sql-server
user3657339
user3657339

Reputation: 627

Storing FOR XML PATH results into a variable

Below code works fine and convert table to HTML. It gives the results as HTML tables but I want to assign this to a variable

How can we assign the output to a variable in below code. 

CREATE PROC dbo.usp_ConvertQuery2HTMLTable (
@SQLQuery NVARCHAR(3000))
AS
BEGIN
   DECLARE @columnslist NVARCHAR (1000) = ''
   DECLARE @restOfQuery NVARCHAR (2000) = ''
   DECLARE @DynTSQL NVARCHAR (3000)
   DECLARE @FROMPOS INT
   DECLARE @out table
        (
        out nvarchar(max)
        )


   SET NOCOUNT ON

   SELECT @columnslist += 'ISNULL (' + NAME + ',' + '''' + ' ' + '''' + ')' + ','
   FROM sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 0)

   SET @columnslist = left (@columnslist, Len (@columnslist) - 1)
   SET @FROMPOS = CHARINDEX ('FROM', @SQLQuery, 1)
   SET @restOfQuery = SUBSTRING(@SQLQuery, @FROMPOS, LEN(@SQLQuery) - @FROMPOS + 1)
   SET @columnslist = Replace (@columnslist, '),', ') as TD,')
   SET @columnslist += ' as TD'
   SET @DynTSQL = CONCAT (
         'SELECT (SELECT '
         , @columnslist
         ,' '
         , @restOfQuery
         ,' FOR XML RAW (''TR''), ELEMENTS, TYPE) AS ''TBODY'''
         ,' FOR XML PATH (''''), ROOT (''TABLE'')'
         )

   PRINT @DynTSQL

   EXEC (@DynTSQL)

   SET NOCOUNT OFF
END

Upvotes: 2

Views: 1224

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

Generally, you have 2 options.

  1. Via an intermediate temporary table (table variable).

By itself, exec() returns nothing when a literal or variable is executed, but you can use the rowset produced by it as a source for an insert statement:

-- Option 1
declare @t table (X xml);
declare @Ret xml;

insert into @t (X)
exec('select top 1 * from sys.objects o for xml raw(''TR''), elements, type;');

select top (1) @Ret = t.X from @t t;

select @Ret as [Option1];
go
  1. Switching to the sys.sp_executesql

As Peter has suggested in the comments, you can switch from exec to the sp_executesql system stored procedure, which provides an additional functionality of output parameters:

-- Option 2
declare @s nvarchar(max) = N'set @A = (select top 1 * from sys.objects o for xml raw(''TR''), elements, type);';
declare @Ret xml;

exec sys.sp_executesql @s, N'@A xml = null output', @A = @Ret output;

select @Ret as [Option2];
go

Upvotes: 1

Related Questions