Using SQL for XML and Nested Table width

Sylvia Manojlovic 0
Viewed 49

The code below will create a table, insert test data, and then format to send as html in email. The profile name needs to be set up in SQL, and the recipient needs to be edited to a valid email address. The resultant email looks as shown below.

create table #results ( docno varchar(50), customer varchar(250), telephone varchar(50),
    docamount money, operator varchar(250),  skuno varchar(50), descr varchar(500),
    quantity int, itemvalue money, batchlineno int, paymethod varchar(50))

insert into #results values ('ORDER1', 'Mickey Mouse', '12345678', 500, 'Joe Bloggs', 'ITEM-1', 'Item Description 1', 1, 300, 1, 'Credit Card')
insert into #results values ('ORDER1', 'Mickey Mouse', '12345678', 500, 'Joe Bloggs', 'ITEM-2', 'Item Description 1', 1, 200, 2, 'Credit Card')
insert into #results values ('ORDER2', 'Mary Poppins', '9008007', 300, 'Bob Marley', 'ITEM-1', 'Item Description 1', 1, 300, 1, 'Credit Card')



declare @body varchar(max) , @docno varchar(50) 
 select @body =   
  '<html>' +  
  '<head>'+  
  '<style> .inkclass {text-align: right;} </style>' +  
  '</head>'+  
  '<body>'+  
  '<h3>' + 'Report'  + '</h3>' 

if  exists(select * from #results)
begin
    declare cur cursor for select distinct docno from #results order by docno 
    open cur
    fetch cur into @docno
    while @@fetch_status = 0
    begin
        select @body +=   
          '<table border="5px" >' +  
          '<tr>'+  
          '<th>Doc. No.</th>'+
          '<th>Customer</th>'+
          '<th>Contact No.</th>'+
          '<th>Staff Member</th>'+
          '<th>Doc. Amount</th>'+
          '<th>Pay Method</th>'+
          '</tr>'  

        select @body = @Body +  cast(( select 
             convert(varchar(80), docno    ) as 'td','', 
             convert(varchar(80), customer    ) as 'td','', 
             convert(varchar(80), telephone     ) as 'td','', 
             convert(varchar(80), operator       ) as 'td','', 
             'text-align:right' as 'td/@style',convert(varchar(15), FORMAT(docamount  , 'N', 'en-us') ) as 'td','',
             convert(varchar(80),paymethod ) as 'td',''
             from #results    where batchlineno = 1 and docno = @docno             
            FOR XML PATH('tr'), TYPE   ) AS NVARCHAR(MAX) )

        select @body += ''

        select @body +=  '<table border="5px" >' +  
          '<tr>'+   
          '<th>Skuno</th>'+
          '<th>Description</th>'+
          '<th>Qty</th>'+
          '<th>Line Total</th>'+
          '</tr>'
        select @body = @Body +  cast(( select
             convert(varchar(80),skuno       ) as 'td','',
             convert(varchar(80),descr       ) as 'td','',
             'text-align:right' as 'td/@style',convert(varchar(15),FORMAT(quantity   , 'N', 'en-us')) as 'td','',
             'text-align:right' as 'td/@style',convert(varchar(15),FORMAT(itemvalue   , 'N', 'en-us')) as 'td',''
             from #results    where docno = @docno order by batchlineno
        FOR XML PATH('tr'), TYPE   ) AS NVARCHAR(MAX) ) 

        select @body += '</table></table><br><br>'

        fetch cur into @docno
    end
    close cur
    deallocate cur
end
select @body += '</body>' + '</html>' 


if exists(select * from #results)
begin
    exec msdb..sp_send_dbmail @profile_name = 'MailProfileNameInSQL' ,
    @recipients = 'whomever@gmail.com' , @subject = 'Transaction Detail Report',
    @body = @body, @body_format = 'html' 
end

drop table #results

Output produced:

output from sql above

Instead, I want to span the nested table columns to take up the full width of the parent table. This is probably easy enough to do in html, but how to do it with the sql for XML option? It should look like this (red arrow showing that the nested table columns should fill that area)

desired output

xhtml xquery xml sql-server sql
1 Answers

Firstly I will preface by saying that SQL Server is the wrong place to be constructing HTML. It only understands XML, so you would have to restrict yourself to XHTML to be compatible.

Ideally you should do this in some kind of scripting or client language, like C#, Python or Powershell. You can for example create a Powershell job in SQL Server Agent to be run on schedule.

Be that as it may:

  • The attribute you need is colspan="100".
  • You want it on the outer table cell.
  • You should probably just use <div> instead, but I haven't bothered with that.
  • You don't need that horrible cursor code. You can just use STRING_AGG and some nested CROSS APPLY with FOR XML to get the result you want.
  • Note the way I create each row using FOR XML, then aggregate it using STRING_AGG. Had you just had a single big table then one FPR XML would have done the trick.
WITH innerTables AS (
    SELECT
      r.docno,
      r.customer,
      r.telephone,
      r.operator,
      r.paymethod,
      r.docamount,
      r.itemvalue,
      innerTable = 
          '<table border="5px" >' +  
          '<tr>'+   
          '<th>Skuno</th>'+
          '<th>Description</th>'+
          '<th>Qty</th>'+
          '<th>Line Total</th>'+
          '</tr>'+
          STRING_AGG(x.innerTable, '') WITHIN GROUP (ORDER BY r.batchlineno)+
          '</table></table><br/><br/>'
  
    FROM #results r
  
    CROSS APPLY (
        SELECT
          v.*
        FROM (VALUES
          (null, r.skuno),
          (null, r.descr),
          ('text-align:right', FORMAT(r.quantity, 'N', 'en-us')),
          ('text-align:right', FORMAT(r.itemvalue, 'N', 'en-us'))
        ) v([@style], [text()])           
        FOR XML PATH('td'), ROOT('tr')
    ) x(innerTable)
  
    GROUP BY
      r.docno,
      r.customer,
      r.telephone,
      r.operator,
      r.paymethod,
      r.docamount,
      r.itemvalue  
)
  
SELECT @body += STRING_AGG(
          '<table border="5px" >'+
          '<tr>'+
          '<th>Doc. No.</th>'+
          '<th>Customer</th>'+
          '<th>Contact No.</th>'+
          '<th>Staff Member</th>'+
          '<th>Doc. Amount</th>'+
          '<th>Pay Method</th>'+
          '</tr>'+
          x.outerTable+
          '<tr><td colspan="100">'+
          r.innerTable+
          '</td></tr>'+
          '</table></table><br/><br/>',
      '')
FROM innerTables r

CROSS APPLY (
    SELECT 
      v.*
    FROM (VALUES
          (null, r.docno),
          (null, r.customer),
          (null, r.telephone),
          (null, r.operator),
          (null, r.paymethod),
          ('text-align:right', FORMAT(r.docamount, 'N', 'en-us')),
          ('text-align:right', FORMAT(r.itemvalue, 'N', 'en-us'))
    ) v([@style], [node()])
    FOR XML PATH('td'), ROOT('tr')
) x(outerTable);

db<>fiddle

Charlieface0
Related