SQL HTML DBMail with multiple queries

  • 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>';

  • 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)

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply