Encoding a SQL Server SELECT statement for HTML output

Question

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

Serg · Accepted Answer · 2019-07-27 14:11:13Z

1

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>

answered Jul 27, 2019 at 14:11

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Over a year ago

This is Perfect THANKS

Collectives™ on Stack Overflow

Encoding a SQL Server SELECT statement for HTML output

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related