Having trouble getting my syntax just right - a little help appreciated. If you scroll to the bottom and note the 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/@colspan],td=comments, '' for xml path('tr'), type) from #D1 with (nolock) where #D1.imredem=@imredem --,(select '3' as [td/@colspan],td=#myPBL.pList, '' for xml path('tr'), type) -- from #myPBL where #myPBL.imredem=@imredem for xml path('')) AS nvarchar(max)) + '</table>';