CODEWITHSUNDEEP
sql-serverxmlsql-server-2008xquery
ca9163d9
ca9163d9

Reputation: 29159

Convert to html using xquery?

I have the following T-Sql for database email.

-- create proc TableToHtml @table varchar(max) as
declare @table varchar(max) = '(select 1 a, ''one'' b union all select 2, ''two'') t '
declare @sql varchar(max) = '
    declare @xml xml = (
        select * from ' + @table + ' 
        for xml path(''tr''), root(''table'')
    ); 
    select @xml'
declare @tmp table (x xml)
insert into @tmp exec(@sql) 
declare @x xml = (select x from @tmp)
select @x

and it returns

<table>
  <tr>
    <a>1</a>
    <b>one</b>
  </tr>
  <tr>
    <a>2</a>
    <b>two</b>
  </tr>
</table>

Is it possible to write the xquery to let it returns the following html?

<table>
  <tr>
    <th>a</th>
    <th>b</th>
  </tr>
  <tr>
    <td>1</td>
    <td>one</td>
  </tr>
  <tr>
    <td>2</td>
    <td>two</td>
  </tr>
</table>

Upvotes: 1

Views: 926

Answers (2)

ca9163d9
ca9163d9

Reputation: 29159

I figured out a less hacking one. The only problem is it will create <td /> instead of <td></td> if the value is null. It will cause some layout issue when the email is sent to some old Outlook clients.

declare @table varchar(max) = '(select 1 a, ''one'' b union all select 2, ''two'') t '
declare @sql varchar(max) = '
    declare @xml xml = (
        select * from ' + @table + ' 
        for xml path(''tr''), root(''table'')
    ); 
    select @xml'
declare @tmp table (x xml)
insert into @tmp exec(@sql) 
declare @x xml = (select x from @tmp)
select @x.query('<body>
<table>
  <tr>
    {for $c in /table/tr[1]/* return element th { local-name($c) } }
  </tr>
  {
    for $r in /table/* 
    return element tr { for $c in $r/* return element td { data($c) } } 
  }
</table>
</body>')

Upvotes: 2

Tim Lehner
Tim Lehner

Reputation: 15251

Major hack alert!

There is probably a more elegant way to do this, but here is the quick hack:

declare @xml xml = (
    select * from (
        select 'a' + '</th><th>' + 'b' as th, null as td
        union all
        select null, '1' + '</td><td>' + 'one'    
        union all
        select null, '2' + '</td><td>' + 'two'    
    ) t
    for xml path('tr'), root('table')
); 
select cast(replace(replace(cast(@xml as varchar(max)), '&lt;/th&gt;&lt;th&gt;', '</th><th>'), '&lt;/td&gt;&lt;td&gt;', '</td><td>') as xml)

Output:

<table>
  <tr>
    <th>a</th>
    <th>b</th>
  </tr>
  <tr>
    <td>1</td>
    <td>one</td>
  </tr>
  <tr>
    <td>2</td>
    <td>two</td>
  </tr>
</table>

Hack list:

  • Use nulls to get th and td in the same "column"
  • Explicitly add your header values a and b
  • Concatenate column values and explicitly put an end and start tag in the middle
  • Convert xml to varchar
  • Replace sanitized versions of those tags with angle bracket versions
  • Convert back to xml

Much of this can probably be refactored to use variables, perhaps gleaned from your input/data.

Note:

Generally speaking, you should let your presentation layer create html for you. You can probably make a generic function in your DB to create html from a table, but there are already so many great presentation classes, controls, etc. that are designed to do exactly this. Your application can almost always scale better than your database.

Upvotes: 0

Related Questions