CODEWITHSUNDEEP
htmlsqlsql-serverxmlt-sql
Neeraj Sharma
Neeraj Sharma

Reputation: 588

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>'
                        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

enter image description here

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

Upvotes: 2

Views: 3628

Answers (4)

Dan Malengela
Dan Malengela

Reputation: 3

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>' ```

Upvotes: 0

Serg
Serg

Reputation: 22811

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('')

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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>

Upvotes: 6

Ahmad Aghazadeh
Ahmad Aghazadeh

Reputation: 17131

Hi and your code use this :

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

Upvotes: 1

Related Questions