CODEWITHSUNDEEP
sql-serverxmlencoding
Bill
Bill

Reputation: 1477

Encoding a SQL Server SELECT statement for HTML output

I have this SELECT statement that I will be using in a SQL Server 2016 stored procedure:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
      DROP Table #TempTable

SELECT DISTINCT      
    STUFF((SELECT ', '+  CASE WHEN AU1.Color IS NOT NULL THEN  +'<b><font color="' + AU1.Color + '">' + AU1.LastName+ ' ' + AU1.Initials + '</font></b>' +  CHAR(10) ELSE
            AU1.LastName + ' ' + AU1.Initials END  AS [text()]
           FROM 
               dbo.[Publication.PubMed.Author] AU1
           WHERE 
               AU1.PMID = 30579772 
           ORDER BY 
               AU1.PMID
           FOR XML PATH('')), 1, 1, '') AS authorList
INTO 
    #TempTable

SELECT * FROM #TempTable

And I want to save it with the < and > and when I have this as part of a larger SELECT statement it does but when I pull it out to run on it's own it does not. I have what I through was the answer with the [text()] but that does not help. The code gets put into an Word document as an HTML string and I it to run correctly. Should I store it encoded and worry about decoding on the endusers side? or can I store the html formatted string?

I get

  Rodenbach RA, Norton SA, Wittink MN, &lt;b&gt;&lt;font color="Blue"&gt;Mohile S&lt;/font&gt;&lt;/b&gt;

and I want 

 Rodenbach RA, Norton SA, Wittink MN, <b><font color="Blue">Mohile S</font></b>

What am I doing wrong? Thanks

Upvotes: 0

Views: 66

Answers (1)

Serg
Serg

Reputation: 22811

Cast the the whole value with markup to xml, don't apply AS [text()]. Demo

select convert(xml, 'my text' + ' <p> my para </p>') 
for xml path ('')

result

my text <p> my para </p>

Upvotes: 1

Related Questions