CODEWITHSUNDEEP
htmlsqlxmlsql-server-2008sql-server-2005
Bartman
Bartman

Reputation: 97

How do you keep HTML tags when querying a xml using sql?

I want to keep html tags in my sql query when i write a query to generate xml tags. For example:

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 do I solve this? Thanks!

Upvotes: 9

Views: 9453

Answers (2)

Iman
Iman

Reputation: 18946

below snippets works for me

DECLARE @HTMLt  NVARCHAR(MAX)  ;

........

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

Upvotes: 3

dan radu
dan radu

Reputation: 2782

If using xhtml, I believe the conversion to Xml will do:

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 is 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

Upvotes: 5

Related Questions