SQL HTML DBMail with multiple queries

• Janet Barnett

  SSChasing Mays

  Points: 604

  More actions

  December 19, 2018 at 9:57 am

  #409392

  Having trouble getting my syntax just right - a little help appreciated.  If you scroll to the bottom and note the green commented area, I can't seem to get that correct so that it will run. 
  I've tried a bunch of stuff, the latest being to just create a temp table and store the data in the temp table and then reference that (see just below), but I've got some syntax slightly off.

  insert into #myPBL (id, pList)
  select rs.id, rs.plist from (select distinct id, stuff((select '~' + code+ ' ' + upper(left(text,1)) + substring(lower(text),2,len(text)-1) from my table with (nolock)
  where (did=24) and (id=#C1.id) for xml path('')),1,1,'')plist from #C1 with (nolock))rs;

  set @tableHTML = @tableHTML + 
N'<table border="1" cellspacing="1" cellpadding="2" style="font-size: 10px;">' + blah blah header
</tr>' +  
cast((  
select (
td=d2.bp, '',
td=d2.[weight], '',
td=d2.next_appt, ''

  for xml path('tr'), type),''

  ,(select '3' as ,td=comments, '' for xml path('tr'), type)

  from #D1 with (nolock) where #D1.imredem=@imredem     

  --,(select '3' as ,td=#myPBL.pList, '' for xml path('tr'), type)

  --from #myPBL where #myPBL.imredem=@imredem

  for xml path('')) AS nvarchar(max)) + '</table>';


• sgmunson

  SSC Guru

  Points: 110651

  More actions

  December 19, 2018 at 11:31 am

  #2016865

  FOR XML PATH(''), TYPE has to follow the end of the query.  It can't be within the SELECT.   Has to be at the end of the query...

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)

• Janet Barnett

  SSChasing Mays

  Points: 604

  More actions

  December 19, 2018 at 11:47 am

  #2016870

  Thanks, but again, I'm having trouble getting the syntax right with two queries back-to-back.  Could you elaborate?