In SQL Server I have written a script for sending the mail from SQL Database where i am using xml for getting data into proper table format. Now i want multiple tables to be displayed in mail from multiple query. some sample document is attached for reference purpose please find it and let me know how to get the desire output. for desire output sample is also attached please let me know if anyone finds the solution for this
DECLARE @xml NVARCHAR(MAX)
DECLARE @xmlP NVARCHAR(MAX)
DECLARE @xmlI NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @body1 NVARCHAR(MAX)
DECLARE @body2 NVARCHAR(MAX)
Declare @count int
declare @per int
set @xml=cast(( SELECT [pdate] as 'td','',[Part_Name] AS 'td','',[shift_name] as 'td','',[ptarget] as 'td','',[pruduction] as 'td','',[Achieved] as 'td','',[RFT] as 'td'
FROM [PCP_Dosser].[dbo].[production_details_all_parts] Where pdate=(select convert( date,GetDate()-1)) and Part_Name='Doser'
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Previous Day Production Details</H3>
<table border=1>
<tr>
<th> Previous Day Doser Line Details</th><.tr>
<th> Date </th> <th>Part Name</th> <th>Shift Name</th><th>Target</th><th>Achieved</th><th>Achieved %</th><th>RFT</th></tr><tr>
</table><br>'
set @xmlI=cast(( SELECT [pdate] as 'td','',[Part_Name] AS 'td','',[shift_name] as 'td','',[ptarget] as 'td','',[pruduction] as 'td','',[Achieved] as 'td','',[RFT] as 'td'
FROM [PCP_Dosser].[dbo].[production_details_all_parts] Where pdate=(select convert( date,GetDate()-1)) and Part_Name='Injector'
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<H3>Previous Injector Line Day Production Details</H3>
<table border=2>
<tr>
<th> Date </th> <th>Part Name</th> <th>Shift Name</th><th>Target</th><th>Achieved</th><th>Achieved %</th><th>RFT</th></tr> <tr>
</table><br>'
SET @xmlP = CAST(( SELECT [pdate] as 'td','',[Part_Name] AS 'td','',[shift_name] as 'td','',[ptarget] as 'td','',[pruduction] as 'td','',[Achieved] as 'td','',[RFT] as 'td'
FROM [PCP_Dosser].[dbo].[production_details_all_parts] Where pdate=(select convert( date,GetDate()-1)) and Part_Name='Pump'
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<H3>Previous Day Pump Production Updates</H3>
<table border = 3>
<tr>
<th> Date </th> <th>Part Name</th> <th>Shift Name</th><th>Target</th><th>Achieved</th><th>Achieved %</th><th>RFT</th></tr>'
SET @body = @body+@xml+@xmlI+@xmlP+'</table></body></html>'
--SET @body2 = @body+@xmlI+'</table></body><br/></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NGMES', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = '[email protected]',
--@copy_recipients = @copy_to,
@subject = 'PCP Production Count Update';
Thanks Team got The answer query is resolved thanks for your help for answer i am pasting the script please check.