How can I set the xml result to the nvarchar declaration in sql

I have the following sql , what I want is the return of the select to be assigned to the @NewCor Nvarchar , but I can’t make it work

  declare @NewCor nvarchar(max) 
   select  
  (select 'ID' as th for xml path(''), type),
  (select 'Cod'   as th for xml path(''), type),
  (select 'Nume'  as th for xml path(''), type)
union all         
select 
  (select p.id     as 'td' for xml path(''), type),
  (select p.cod           as 'td' for xml path(''), type),
  (select p.nume as 'td' for xml path(''), type)
from #TempCor p 
for xml path('tr')

Answer

I am so sure about the internal structure of your temp table, so you may need to tweak a few things to match your use case. But generally this should give you an idea of how to do that:

declare @NewCor nvarchar(max);
select CONVERT(xml, N'<table><tr><th>a</th><th>Cod</th><th>Nume</th></tr><tr><td>1</td><td>2</td><td>3</td></tr><tr><td>1</td><td>2</td><td>3</td></tr></table>') [type] into #TempCor;

;with [base] as (
    select top 1
        T.TR.value(N'(th)[1]', N'nvarchar(max)') as [1],
        T.TR.value(N'(th)[2]', N'nvarchar(max)') as [2],
        T.TR.value(N'(th)[3]', N'nvarchar(max)') as [3],
        0 [Order]
    from [#TempCor] [p]
    cross apply [p].[type].nodes(N'(/table/tr)') [T](TR)
    union all
    select
        T.TR.value(N'(td)[1]', N'nvarchar(max)'),
        T.TR.value(N'(td)[2]', N'nvarchar(max)'),
        T.TR.value(N'(td)[3]', N'nvarchar(max)'),
        ROW_NUMBER() OVER(ORDER BY GETDATE())
    from [#TempCor] [p]
    cross apply [p].[type].nodes(N'(/table/tr)') [T](TR)
    where
        T.TR.value(N'(td)[1]', N'nvarchar(max)') is not NULL or
        T.TR.value(N'(td)[2]', N'nvarchar(max)') is not NULL or
        T.TR.value(N'(td)[3]', N'nvarchar(max)') is not NULL
), [rec] as (
    select N'<tr><th>'+[1]+N'</th><th>'+[2]+N'</th><th>'+[3]+N'</th></tr>' as [string], [Order]
    from [base]
    where [Order] = 0
    union all
    select [rec].[string]+N'<tr><td>'+[1]+N'</td><td>'+[2]+N'</td><td>'+[3]+N'</td></tr>', [base].[Order]
    from [rec] join [base] on [base].[Order] = [rec].[Order] + 1
)
select @NewCor=[string]
from [rec]
where [Order] = (select MAX([Order]) from [base]);

Basically:

• Extract the data from XML, so you get a normal SQL table
• The recursively combine the data
• Finally: Assign the outcome (which is where Order is maxed) to a variable