Sql Server create html tags

Question

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>'
                        from(
                        select ('<p>'+UploadDocumentName+'</p><a target=''_blank'' class=''ml10'' href='''+DocumentFilePath+'''title=''Download''>') As Filepath from table1 CLRD 
                         where                      
                        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'>
<li><p>bill.png</p>
<a target='_blank' class='ml10' href='2c0a7c0c-d228-4f5d-9a8f-eb32911509db.png' title='Download'>
</a>
</li>
</ul>

Table structure

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

what kind of server side language do you use to access sql server from html? Why don't you use that language to build the html? — user2399035
– user2399035, Commented Aug 30, 2016 at 8:51
I am using C#, But i am trying this in stored procedure. Yes i can do this thing in C# or in scripting language but want in sp(sql-server). How i prevent encode tag in html query? — Neeraj Sharma
– Neeraj Sharma, Commented Aug 30, 2016 at 8:58
You should (almost) never create XML by concatenating strings. You'll get into hell if your strings contain forbidden characters. You might trick around but you should - if ever possible - build the XML properly. And better use a scalar function than a stored procedure. This is much easier to query... — Gottfried Lesigang
– Gottfried Lesigang, Commented Aug 30, 2016 at 9:01

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

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]
FOR XML PATH('ul')

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

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

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));
INSERT INTO @tbl VALUES
 ('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]
      ,(
        SELECT
           '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
       )
FOR XML PATH('ul')

This is the result

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 30, 2016 at 9:10

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

4 Comments

Steve Cooper Over a year ago

Definitely investigate FOR XML -- it does all the escaping for you and SQL server just doesn't have the power with string manipulation to do it right. There's no value in hand-rolling your own attempt at escaping characters. IIRC there are differences in HTML and XML escaping (maybe apostrophes?) which can be evil...

Gottfried Lesigang Over a year ago

@SteveCooper Is this really a comment to my answer?

Neeraj Sharma Over a year ago

@Shnugo My table have multiple results( multiple <li>). at that time how i can manage this with xhtml

Neeraj Sharma Over a year ago

Yes, I am going to :)

Ahmad Aghazadeh · Accepted Answer · 2016-08-30 08:55:44Z

1

Hi and your code use this :

replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )

answered Aug 30, 2016 at 8:55

Ahmad Aghazadeh

17.2k13 gold badges105 silver badges100 bronze badges

1 Comment

Neeraj Sharma Over a year ago

Yes, This is method to replace these ones, but i am trying to find another solution. This fails when a query returns multiple html encoded chars. then how many time we will use replace keyword.

Serg · Accepted Answer · 2016-08-30 09:49:08Z

1

First, your markup is wrong. It should be '<P>'+UploadDocumentName+'</P><a target=''_blank'' class=''ml10'' href='''+DocumentFilePath+''' title=''Download''/>' Use regular XML building features

    select        
       [ul/@class]='downloaditems-grid',
       [ul]=(
       select [P]=UploadDocumentName,
                [a/@target]='_blank',
                [a/@class] ='ml10',
                [a/@href]=DocumentFilePath,
                [a/@title]='Download' 
       from ( 
              -- sample data
              values
               ('123','ad/df')
              ,('456','xx/yy')
            ) CLRD (UploadDocumentName, DocumentFilePath)
       for xml path('li'), type
       )
for xml path('')

edited Aug 30, 2016 at 9:49

answered Aug 30, 2016 at 9:34

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Comments

Dan Malengela · Accepted Answer · 2023-01-25 09:54:48Z

0

Here is an implementation for creating a HTML option input using the values from a database table.

SELECT 'output', '<div class="form-group">' + (
 SELECT       
       [select/@class]='form-control m-bot15',
       [select/@cntrl]='Sample',
       [select]=(
       SELECT` 
            [option/@value] = column_id, 
            [option]= column_description
       FROM 
              -- data from database table
                Table_Name
            
       FOR XML PATH(''), TYPE
       )
FOR XML PATH('') 
  ) + '</div>' ```

answered Jan 25, 2023 at 9:54

Dan Malengela

31 bronze badge

Collectives™ on Stack Overflow

Sql Server create html tags

4 Answers 4

Short explanation:

UPDATE use with table data

4 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Short explanation:

UPDATE use with table data

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related