SQL TO THML TABLE

最新推荐文章于 2022-07-01 16:13:53 发布

xiaonibaba

最新推荐文章于 2022-07-01 16:13:53 发布

阅读量203

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/a22698488/article/details/104902990

版权

sql 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

经常用到存储过程,发邮件给用户,这样写,可以节省大量的SQL
拼接字符串

--写法1
SELECT '<table>' + CAST((SELECT TOP 100 
       td=lngEmployeeID ,''
      ,td=strEmployeeCode, '' 
      ,td=strEmployeeName,'' 
from Syn_Employee 
for XML PATH('tr'), TYPE) AS  nvarchar(MAX)) + '</table>'

--写法2
SELECT '<table>' + CAST((SELECT  TOP 10 * from Syn_Employee FOR XML PATH('tr'), TYPE).query('
   for $item in /tr
   return
     <tr>
      <td>{ $item/lngEmployeeID/text() }</td>
      <td>{ $item/strEmployeeCode/text() }</td>
      <td>{ $item/strEmployeeName/text() }</td>
     </tr>
') AS NVARCHAR(MAX)) + '</table>' as Result;


--demo 3
SELECT CONVERT(NVARCHAR(MAX), '<html><head><META content="text/html;"></head>
<body>
<style>
table,td { margin: 0; padding: 0; border: 1px solid #CCC; border-collapse: collapse; font: small/1.5 "Tahoma", "Bitstream Vera Sans", Verdana, Helvetica, sans-serif; }
table { white-space: nowrap; border: none; border: 1px solid #CCC; }
thead th, tbody th { background: #FFF; color: #000; padding: 2px 4px; border-left: 1px solid #CCC; }
tbody th { background: #fafafb; border-top: 1px solid #CCC; text-align: center; font-weight: normal; }
tbody tr td { padding: 0px 4px; color: #000; }</style>'+(SELECT

    (SELECT '表头' FOR XML PATH(''), TYPE) AS 'caption',
    (SELECT '列1' AS th, '列2' AS th FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'thead',
    (
        SELECT TOP 10 lngEmployeeID AS td,strEmployeeName AS td from Syn_Employee 

    FOR XML RAW('tr'), ELEMENTS, TYPE
    ) AS 'tbody'
  FOR XML PATH(''), ROOT('table'))+'</body></html>');