Sql select query set html tags using for xml path

Question

I am having two queries which retuns data containing html tag. Sql Query 1 works fine but Sql Query 2 not returns exact html tag, query 2 data is nested html tag.

Query 1 :

declare @val1 nvarchar(max)
declare @val2 nvarchar(max)
set @val1='my value one';
set @val2='my value two';
select data=( select @val1 as td, '' ,@val2 as td for xml path('tr'))

OutPut:

<tr><td>my value one</td><td>my value two</td></tr>

---

Query 2 :

 select stuff(( select ','+'<span>'+@val1+'<span>'+@val2+'</span></span>'       
                  for xml path('')),1,1,'') as Col1

Output:

<span>my value one<span>my value two</span></span>

Desired Output:

<span>my value one<span>my value two</span></span>

Answer 1

use correctly xml syntax:

declare @val1 nvarchar(max)
declare @val2 nvarchar(max)
set @val1='my value one';
set @val2='my value two';
select data=( select @val1 as td, '' ,@val2 as td for xml path('tr'))

select stuff(( select convert(xml,','+'<span>'+@val1+'<span>'+@val2+'</span></span>') for xml path('')),1,1,'') as Col1

before convert your string in xml

output : <span>my value one<span>my value two</span></span>

example sql fiddle