Neeraj Sharma Neeraj Sharma - 11 months ago 51
SQL Question

Sql Server create html tags

I am generating html from stored procedure, But it encode html tags into '<', '>' or '&'. I require tags as I generate them. I have tried with [CDATA] but did not get the result I expected.

select '<ul class=''downloaditems-grid''>'+(
select stuff(
select '<li>'+ convert(nvarchar(max),Filepath) +'</li>'
select ('<p>'+UploadDocumentName+'</p><a target=''_blank'' class=''ml10'' href='''+DocumentFilePath+'''title=''Download''>') As Filepath from table1 CLRD
isnull(CLRD.IsDeleted,0) <> 1 and orderid=2
as T for xml path('')),1,2,'')) +' </ul>' a

It returns

<ul class='downloaditems-grid'>t;li&gt;&lt;P&gt;bill.png&lt;/&gt;&lt;a target='_blank' class='ml10' href='2c0a7c0c-d228-4f5d-9a8f-eb32911509db.png'title='Download'&gt;&lt;/li&gt; </ul>

But my requirement is:

<ul class='downloaditems-grid'>
<a target='_blank' class='ml10' href='2c0a7c0c-d228-4f5d-9a8f-eb32911509db.png' title='Download'>

Table structure

enter image description here

Can any one give some hints, other than use replace how i decode there while creating html tags?

Answer Source

Your "generated" XML is invalid in several points of view...

With SQL Server you can easily create valid XHTML like this:

SELECT 'downloaditems-grid' AS [@class]
      ,'bill.png' AS [li/p]
      ,'_blank' AS [li/a/@target]
      ,'ml10' AS [li/a/@class]
      ,'2c0a7c0c-d228-4f5d-9a8f-eb32911509db.png' AS [li/a/@href]
      ,'Download' AS [li/a/@title]

The result (which seems to be quite similar to the one you tried

<ul class="downloaditems-grid">
    <a target="_blank" class="ml10" href="2c0a7c0c-d228-4f5d-9a8f-eb32911509db.png" title="Download" />

And be aware a HTML is dirty and absolutely not the same as XHTML.

Short explanation:

In XML there is a sharp separation between the tags and attribute names and the content. Some characters are absolutely forbidden out of clear reasons (<, > and & and many special characters can lead to unexpected results in combination with the string encoding. Tags and attributes have clearly documented limitations. The content may be anything, but: The content will ever need correct escaping, therefore your forbidden characters where transformed into xml entities

CDATA won't help you as it is not supported by FOR XML PATH (although there is EXPLICIT...), anyway, CDATA would not solve your problem, even if it worked...

Your concatenated xml string was invalid in many points of view (e.g. </> or missing blank...)

UPDATE use with table data

Here is the code to use this with table data. Be aware, that I added forbidden characters to line 3!

DECLARE @tbl TABLE(DocumentFilePath VARCHAR(100),UploadDocumentName VARCHAR(100));
 ('File1.png','This is file 1')
,('File2.png','This is file 2')
,('File&3.png','This is file 3& with forbidden <>!!')

SELECT 'downloaditems-grid' AS [@class]
           'bill.png' AS [p]
          ,'_blank' AS [a/@target]
          ,'ml10' AS [a/@class]
          ,tbl.DocumentFilePath AS [a/@href]
          ,tbl.UploadDocumentName AS [a/@title]
      FROM @tbl AS tbl
      FOR XML PATH('li'),TYPE

This is the result

<ul class="downloaditems-grid">
    <a target="_blank" class="ml10" href="File1.png" title="This is file 1" />
    <a target="_blank" class="ml10" href="File2.png" title="This is file 2" />
    <a target="_blank" class="ml10" href="File&amp;3.png" title="This is file 3&amp; with forbidden &lt;&gt;!!" />