Menu
I'm getting an XML to my stored procedure and I need to iterate over it and send emails according to the XML.
I have another stored procedure just for sending the email:
MailingSystem..SP_SendEmail
@Cc = N'', -- nvarchar(500)
@Bcc = N'', -- nvarchar(500)
@FromEMailAddress = N'', -- nvarchar(500)
@ReplyToEMailAddress = N'', -- nvarchar(500)
@Subject = N'', -- nvarchar(500)
@EmailRecipient = N'', -- nvarchar(500)
@ContentType = N'', -- nvarchar(50)
@Content = N'', -- nvarchar(max)
Here is the structure of the XML:
DECLARE @XMLData XML =
'<GenericXML>
<GenericEmailsNotification>
<EmailMessage>
<From>[email protected]</From>
<Recipients>[email protected]</Recipients>
<CC>[email protected]</CC>
<Subject>Follow The Sun </Subject>
<Body>Breathe, breathe in the air. Set your intentions.Dream with care. </Body>
</EmailMessage>
<EmailMessage>
<From>[email protected]</From>
<Recipients>[email protected]</Recipients>
<Subject>Second Email is best</Subject>
<Body>Second Email body...</Body>
</EmailMessage>
</GenericEmailsNotification>
</GenericXML>'
I want to loop over each EmailMessage node and extract the data related to this node/email (Subject, From, Recipients, Body and etc) and send it to the stored procedure that in charge of sending the mail.
Each XML can have multiple emails to send (if its not clear until now..)
How can I do it ? there is any way to loop over the XML elements ?
Thanks in advance
617
You could parse XML and use cursor:
DECLARE @XMLData XML =
'<GenericXML>
<GenericEmailsNotification>
<EmailMessage>
<From>[email protected]</From>
<Recipients>[email protected]</Recipients>
<CC>[email protected]</CC>
<Subject>Follow The Sun </Subject>
<Body>Breathe,breathe in the air.Set your intentions.Dream with care</Body>
</EmailMessage>
<EmailMessage>
<From>[email protected]</From>
<Recipients>[email protected]</Recipients>
<Subject>Second Email is best</Subject>
<Body>Second Email body...</Body>
</EmailMessage>
</GenericEmailsNotification>
</GenericXML>' ;
Query:
SELECT [from] = s.c.value('(./From)[1]', 'nvarchar(250)')
,[Recipients] = s.c.value('(./Recipients)[1]', 'nvarchar(250)')
,[CC] = s.c.value('(./CC)[1]', 'nvarchar(250)')
,[Subject] = s.c.value('(./Subject)[1]', 'nvarchar(250)')
,[body] = s.c.value('(./Body)[1]', 'nvarchar(MAX)')
INTO #Emails
FROM @XMLData.nodes('/GenericXML/GenericEmailsNotification/EmailMessage')
AS s(c);
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT [from],[Recipients],[CC],[Subject],[body] FROM #Emails;
DECLARE @from NVARCHAR(250),
@recipients NVARCHAR(250),
@cc NVARCHAR(250),
@subject NVARCHAR(250),
@body NVARCHAR(MAX);
OPEN cur;
FETCH NEXT FROM cur INTO @from, @recipients, @cc, @subject, @body;
WHILE @@FETCH_STATUS = 0
BEGIN
-- send email, pass variables to SP call, handle `NULL` with `ISNULL` if needed
-- EXEC MailingSystem..SP_SendEmail @Subject = @subject, ...
SELECT 'Send email in cursor loop:', @from, @recipients, @cc, @subject, @body;
FETCH NEXT FROM cur INTO @from, @recipients, @cc, @subject, @body;
END
CLOSE cur;
DEALLOCATE cur;
LiveDemo
178
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
