How do you save HTML tags when xml request using sql?

Question

I want to save html tags in my sql query when I write a query to generate xml tags. For instance:

select '<p> this is a code</p>' as code from table name for xml path (''), type 

outputs:

 <code>&ltp&gt; this is a code &lt/p&gt; <code> 

what it should output:

 <code><p> this is a code </p><code> 

How can i solve this? Thanks!

Answer 1

If you use xhtml , I am sure that the conversion to Xml will be:

 select convert(xml, '<p> this is a code</p>') as code from table name for xml path (''), type 

EDIT: if the column is ntext , implicit conversion to Xml supported:

 create table #t(html ntext) insert into #t values(N'<p> this is a code</p>') select convert(xml, html) as code from #t for xml path (''), type drop table #t 

Answer 2

below snippets works for me

 DECLARE @HTMLt NVARCHAR(MAX) ; ........ SET @HTMLt = REPLACE(REPLACE(REPLACE(@HTMLt,'&amp;','&' ) , '&lt;','<'),'&gt;','>');