使用 xquery 转换为 html?

2021-10-02 00:00:00 xml xquery sql-server-2008 sql-server

我有以下数据库电子邮件的 T-Sql.

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

然后它返回

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

是否可以编写 xquery 让它返回以下 html?

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>

推荐答案

我想出了一个更少黑客攻击的方案.唯一的问题是如果值为空，它将创建 而不是 .将电子邮件发送到某些旧 Outlook 客户端时会导致一些布局问题.

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

相关文章