CODEWITHSUNDEEP
sqlsql-server
Kyle B.
Kyle B.

Reputation: 5787

SQL STUFF() function keeps escaping my HTML

Using the tutorial here:

http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html

I wrote the following query:

SELECT DISTINCT STUFF( (SELECT '<li>' + CountryCode + '</li>' from Country  FOR XML PATH('')),1,1,'') as Codes FROM Country

This works great, but all my HTML is now encoded (i.e. &lt;li&gt;)

Is there a way around this? Using a SUBSTRING() approach does this also.

Upvotes: 3

Views: 2821

Answers (2)

SQLMenace
SQLMenace

Reputation: 134933

it is because XML PATH makes it xml safe so < becomes &lt;

try

DECLARE @CountryCode VARCHAR(MAX)
SELECT @CountryCode = ''
SELECT  @CountryCode = @CountryCode + '<li>' + CountryCode + '</li>' from Country 

SELECT @CountryCode

example

DECLARE @CountryCode VARCHAR(MAX)
SELECT @CountryCode = ''
SELECT   @CountryCode = @CountryCode + '<li>' + name + '</li>' 
FROM master..spt_values
WHERE type = 'a'

SELECT @CountryCode

you can also of course use the REPLACE function to replace '&lt;' to '<'

In my opinion adding li should be done at the front end and not in the database

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Change '<li>' + CountryCode + '</li>' to CountryCode as li and you will probably get what you want.

Upvotes: 4

Related Questions