Generating HTML with SQL query

Question

I have table where content have html values and trying to concatenate them with

<ul> <li>

I have used below query

CREATE TABLE #T(Value varchar(1000))
INSERT INTO #T
values('<p><b>AA</b> something 1</p>'),('<p><b>BB</b> something 2</p>'),('<p><b>CC</b> something 3</p>')

select *
from #T


SELECT '<ul>' +STUFF((SELECT '<li>' + Value + '</li>' 
              FROM #T
              FOR XML PATH('')), 1, 0, '') + '</ul>' AS Notes

Is there anyway to get the HTML as it is, so output will be as below

<ul>
<li><p><b>AA</b> something 1</p></li>
<li><p><b>BB</b> something 2</p></li>
<li><p><b>CC</b> something 3</p></li>
</ul>

Not like

<ul>&lt;li&gt;&lt;p&gt;&lt;b&gt;AA&lt;/b&gt; something 1&lt;/p&gt;&lt;/li&gt;&lt;li&gt;&lt;p&gt;&lt;b&gt;BB&lt;/b&gt; something 2&lt;/p&gt;&lt;/li&gt;&lt;li&gt;&lt;p&gt;&lt;b&gt;CC&lt;/b&gt; something 3&lt;/p&gt;&lt;/li&gt;</ul>

What language are you using the deliver the data from the database to the client side? — John Bell
– John Bell, Commented Dec 18, 2017 at 10:01
Could you try renaming your question? This is not going to attract any help to your question and if this gets a good answer people will not be able to find it in the future because of this title — Tom
– Tom, Commented Dec 18, 2017 at 10:12

Jayasurya Satheesh · Accepted Answer · 2017-12-18 11:21:38Z

3

You getting the results like this because there is no > and < symbols (As well as Ampersand, Double Quotes, and Apostrophy ) Can't be stored in XML as it is, so it will be converted to some codes like &lt ; for <.

Just wrap Notes with a replace function to replace the < and > symbols

CREATE TABLE #T(Value varchar(1000))
INSERT INTO #T
values('<p><b>AA</b> something 1</p>'),('<p><b>BB</b> something 2</p>'),('<p><b>CC</b> something 3</p>')

;with html
as
(
    SELECT '<ul>' +STUFF((SELECT '<li>' + Value + '</li>' 
              FROM #T
              FOR XML PATH('')), 1, 0, '') + '</ul>' AS Notes
)
select
    Notes = REPLACE(REPLACE(Notes,'&lt;','<'),'&gt;','>')
    from html

edited Dec 18, 2017 at 11:21

answered Dec 18, 2017 at 11:02

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

Generating HTML with SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related