這只是筆記本

透過SQL SERVER的XML物件匯出成HTML的格式，
要小心，資料太多可能會造成欄位字串長度過長，後面的文字就會被截斷囉！
(nVARCHAR(max) 最長為4000個字)

set @qString =
‘<H1>’ + @fSubject + ‘</H1>’ +
‘<table border=1>’ +
‘<tr><th>工號</th>’ + ‘<th>姓名</th>’ +
‘<th>職稱</th>’ + ‘<th>職等</th>’ + ‘<th>部門</th>’ + ‘<th>直間接</th>’ +
‘<th>身份類別</th>’ + ‘<th>歸屬日期</th>’ + ‘<th>上班日</th>’ + ‘<th>上班時</th>’ +
‘<th>下班日</th>’ + ‘<th>下班時</th>’ + ‘<th>班別</th>’ + ‘<th>遲到分鐘</th>’ +
‘<th>早退分鐘</th>’ + ‘<th>請假狀態</th>’ +
‘<th>請假狀態</th></tr>’ +
CAST ( ( SELECT td = a.[工號] , ” ,
[td/@style] =’color:red; font-weight:bold’,  –此為可以在下面的姓名欄位設定屬性
td = a.[姓名] , ” ,
td = a.[職稱] , ” ,
td = a.[職等] , ” ,
td = a.[部門] , ” ,
TD = a.[直間接] , ” ,
td = a.[身份類別] , ” ,
td = a.[歸屬日期] , ” ,
–td = a.[上班日] when ‘漏刷’ then ‘<b>’ + a.[上班日] + ‘</b>’ else a.[上班日] end , ” ,
td = a.[上班日] , ” ,
td = a.[上班時] , ” ,
td = a.[下班日] , ” ,
td = a.[下班時] , ” ,
td = a.[班別] , ” ,
td = a.[遲到分鐘] , ” ,
td = a.[早退分鐘] , ” ,
td = isnull(a.[請假狀態],”) ,  ” ,
td = isnull(a.[請假狀態],”)
FROM  card.dbo.ChkM_AttendanceException(@bmonth) as a
where [職等] < 7 and substring([部門],1,2) not in (‘C2′,’C3′,’C4′,’C5’)
order by [部門] , [工號] ,[歸屬日期]
FOR XML PATH(‘tr’), TYPE
) AS nVARCHAR(max) ) +
‘</table>’ ;