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>John@Google.com</From> <Recipients>Naomi@Yahoo.com</Recipients> <CC>Tania@HotMail.com</CC> <Subject>Follow The Sun </Subject> <Body>Breathe, breathe in the air. Set your intentions.Dream with care. </Body> </EmailMessage> <EmailMessage> <From>ron@Google.com</From> <Recipients>Fake@Yahoo.com</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
No comments:
Post a Comment