SQLTeam.com | Weblogs | Forums

How to send query content as xml zip file to an email according to row

Hi All,

I have a table with 2 column: Email, Content.

The Content data look like this:-

declare @table Table (
Email nvarchar(max),
Content notes

insert into @table
select 'test@gmail.com','<?xml version="1.0"?>
<licenseAttachment ver="1.0">
		<entitlement id="XXX-84553-122222">
			<owner name="Corporation 128" siteID="84553" />
				<activation id="XXX-QY59-KYST-H681">
					<catalogName>Plt 2022 Control Client Unlimited Tags</catalogName>

This can be multiple row. So each content (row data) have to send seprately email based on email column as recipients.

How can I send an email with this content as attachment .xml.zip?

Please advise.



Maybe this using powershell? can also be done with ssis script c#

$Username = "MyUserName";
$Password = "MyPassword";

function Send-ToEmail([string]$email, [string]$attachmentpath){

    $message = new-object Net.Mail.MailMessage;
    $message.From = "Micheale@gmail.com";
    $message.Subject = "Monthly Report";
    $message.Body = "Greetings, Attached you will find";
    $attachment = New-Object Net.Mail.Attachment($attachmentpath);

    $smtp = new-object Net.Mail.SmtpClient("localhost", "25");
    #$smtp.EnableSSL = $true;
    $smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
    write-host "Mail Sent" ; 
$sql = "SELECT * from boomshaka"
$Rows = Invoke-Sqlcmd -ServerInstance localhost -Database grafana -Query $sql

ForEach( $Row in $Rows)
   	$file = "D:\reports\" + $Row.Email + ".xml"
    $Row.cpu | Out-File -FilePath $file
    Send-ToEmail  -email $Row.Email -attachmentpath $file;

Hi Sir,

I will give a try & come back to you.


As a bit of a sidebar, this is one of the major reasons why I hate PowerShell. Having credentials in clear text code like this is a major security violation where I work. It should be where you work, as well.

$Username = "MyUserName";
$Password = "MyPassword";

They need to be written as "Trusted Connections" based on the current login.

1 Like

100% corrent @JeffModen but this is just as an example of course you would not have this in plaint text. Also this is the credentials for the email/smtp not sql server.
also this would be schedule and saved in a location where only the Admin would have access to and already knows the user name and password or it could be configured as a env var or some other sort of more secure way.

or person scheduling this, admin, would provide username and password as parameter.

powershell is not the issue, any script/app should never use the above method of clear text password

as @JeffModen correctly mentions the clear text password should not be used. Instead maybe you leverage $Env

$Env:smtp_user = 
$Env:smtp_password = 
1 Like

It's working.