Concatenating html comma seperated using for xml < <

Question

I am trying to write a stored procedure that will convert the results of a SQL query into a HTML string which will display them in a HTML table.

The problem I have is I am using FOR XML to get a comma-separated list of td values and then trying to concatenate it, however when I do that, it loses the < and > characters and replaces them with the XML &lt and &gt - I need it to show me it as <>.

here is the part of the query that does this:

    declare C cursor for
    select (convert(nvarchar(max),(SELECT "th/@width"='50', th = name
    FROM tempdb.sys.columns isc
    WHERE object_id = OBJECT_ID('tempdb.dbo.##tempcolumns') 
            for xml PATH(N''), TYPE)))
    open C
    declare @L varchar(max)
    fetch next from C into @L
    close C
    deallocate C

    DECLARE @html nvarchar(max), @table nvarchar(max), @test nvarchar(max), @html2 nvarchar(max) 
    SET @html =  
    N'<html> <head><title>TestHTML</title></head>' + CHAR(10) + 
    N'<body style="font-family: Arial;">' + 
    N'<h1>Companies2</h1></font>' + 
    '<table style="float: left; width:100%;">' + 
    N'<tr>' + 
            @L +
    N'</tr>' + CHAR(10); 

    SELECT @table = CONVERT(nvarchar(max),(SELECT (
    SELECT  '<td>' + STUFF( ( SELECT td = ', '  + '[' + isc.name + ']' 
    FROM tempdb.sys.columns isc
    WHERE isc.object_id = OBJECT_ID('tempdb.dbo.##tempcolumns') FOR XML PATH ('')), 1,5,'')) FROM ##temp FOR XML PATH(''), TYPE))


    SET @html = @html + @table + CHAR(10) +  
                N'</table></body></html>' 

    SELECT @table

However, this is what it returns:

&lt;td&gt; [Reasons_Code]&lt;/td&gt;&lt;td&gt;, [Open]&lt;/td&gt;&lt;td&gt;, [Ordered]&lt;/td&gt;&lt;td&gt;, [Partially Ordered]&lt;/td&gt;&lt;td&gt;, [Lost]&lt;/td&gt;&lt;td&gt;, [Not Pursued]&lt;/td&gt;&lt;td&gt;, [Expired (Past 90 days)]&lt;/td&gt;&lt;td&gt;, [Pending]&lt;/td&gt;&lt;td&gt; [Reasons_Code]&lt;/td&gt;&lt;td&gt;, [Open]&lt;/td&gt;&lt;td&gt;, [Ordered]&lt;/td&gt;&lt;td&gt;, [Partially Ordered]&lt;/td&gt;&lt;td&gt;, [Lost]&lt;/td&gt;&lt;td&gt;, [Not Pursued]&lt;/td&gt;&lt;td&gt;, [Expired (Past 90 days)]&lt;/td&gt;&lt;td&gt;, [Pending]&lt;/td&gt;&lt;td&gt; [Reasons_Code]&lt;/td&gt;&lt;td&gt;, [Open]&lt;/td&gt;&lt;td&gt;, [Ordered]&lt;/td&gt;&lt;td&gt;, [Partially Ordered]&lt;/td&gt;&lt;td&gt;, [Lost]&lt;/td&gt;&lt;td&gt;, [Not Pursued]&lt;/td&gt;&lt;td&gt;, [Expired (Past 90 days)]&lt;/td&gt;&lt;td&gt;, [Pending]&lt;/td&gt;&lt;td&gt; [Reasons_Code]&lt;/td&gt;&lt;td&gt;, [Open]&lt;/td&gt;&lt;td&gt;, [Ordered]&lt;/td&gt;&lt;td&gt;, [Partially Ordered]&lt;/td&gt;&lt;td&gt;, [Lost]&lt;/td&gt;&lt;td&gt;, [Not Pursued]&lt;/td&gt;&lt;td&gt;, [Expired (Past 90 days)]&lt;/td&gt;&lt;td&gt;, [Pending]&lt;/td&gt;&lt;td&gt; [Reasons_Code]&lt;/td&gt;&lt;td&gt;, [Open]&lt;/td&gt;&lt;td&gt;, [Ordered]&lt;/td&gt;&lt;td&gt;, [Partially Ordered]&lt;/td&gt;&lt;td&gt;, [Lost]&lt;/td&gt;&lt;td&gt;, [Not Pursued]&lt;/td&gt;&lt;td&gt;, [Expired (Past 90 days)]&lt;/td&gt;&lt;td&gt;, [Pending]&lt;/td&gt;&lt;td&gt; [Reasons_Code]&lt;/td&gt;&lt;td&gt;, [Open]&lt;/td&gt;&lt;td&gt;, [Ordered]&lt;/td&gt;&lt;td&gt;, [Partially Ordered]&lt;/td&gt;&lt;td&gt;, [Lost]&lt;/td&gt;&lt;td&gt;, [Not Pursued]&lt;/td&gt;&lt;td&gt;, [Expired (Past 90 days)]&lt;/td&gt;&lt;td&gt;, [Pending]&lt;/td&gt;&lt;td&gt; [Reasons_Code]&lt;/td&gt;&lt;td&gt;, [Open]&lt;/td&gt;&lt;td&gt;, [Ordered]&lt;/td&gt;&lt;td&gt;, [Partially Ordered]&lt;/td&gt;&lt;td&gt;, [Lost]&lt;/td&gt;&lt;td&gt;, [Not Pursued]&lt;/td&gt;&lt;td&gt;, [Expired (Past 90 days)]&lt;/td&gt;&lt;td&gt;, [Pending]&lt;/td&gt;&lt;td&gt; [Reasons_Code]&lt;/td&gt;&lt;td&gt;, [Open]&lt;/td&gt;&lt;td&gt;, [Ordered]&lt;/td&gt;&lt;td&gt;, [Partially Ordered]&lt;/td&gt;&lt;td&gt;, [Lost]&lt;/td&gt;&lt;td&gt;, [Not Pursued]&lt;/td&gt;&lt;td&gt;, [Expired (Past 90 days)]&lt;/td&gt;&lt;td&gt;, [Pending]&lt;/td&gt;

How do I get it to show for example <td> [Reasons_Code] </td> and not <td> [Reasons_Code]</td&gt

You should not build XML via string concatenation! Using a `CURSOR` is something one should avoid too. Your code shows *procedural thinking* while one should think *set based*. As you are trying to build up an `HTML` table obviously, you might read this: https://stackoverflow.com/q/7086393/5089204. Read the younger answers (especially mine :-) ) to find much better approaches for this... — Shnugo, Nov 21 '17 at 12:39
Well, than it's appropriate to close this as duplicate... Happy coding! — Shnugo, Nov 21 '17 at 17:51

iamdave · Answer 1 · 2017-11-21T12:20:21.360

0

By stacking for xml statements, you are telling SQL Server that you want to hold values that contain reserved XML characters in an XML string, for example:

<ActualTag>Your values that contain a < and > special character</ActualTag>

This is obviously impossible. Just because you are using tricks to remove the tags that would otherwise be present, you are still dealing with XML values.

Either change your approach to your string building or just select the value held in your final XML variable:

declare @x xml = '<ActualTag>Your values that contain a &lt; and &gt; special character</ActualTag>';
select @x.value('.','nvarchar(max)')

Output:

Your values that contain a < and > special character

edited Nov 21 '17 at 12:20

answered Nov 21 '17 at 12:12

iamdave

12,023
3
24
53

1

To be honest: XML should not be created on string level at all... In this case - at least I think so - the closest hint might be `SELECT CAST(@SomeStringThatLooksLikeXML AS XML)`, MIght be the OP is looking for `, TYPE` in connection with your suggested `.value()` - hint. But the whole approach is wrong... – Shnugo Nov 21 '17 at 12:40
@Shnugo Oh certainly, agree on all points. – iamdave Nov 21 '17 at 13:01

Concatenating html comma seperated using for xml < <

1 Answers1