SQL Split XML report from single email trigger to multiple email

Question

I have below SQL Stored procedure which emails failed order report . But it sends all the failed orders in a single email with a dotted line demarcation.(like below)

Order Rejected 12345   
    Sold-To Party: abc_customer

    Shipping Address:
    abc customer
    11632 
    US

    Item 1
    Price 100$
    Quantity: 1.0000
    --------------------------------------------------------------------------------
    Order Rejected 56789

    Sold-To Party: xyz_customer

    Shipping Address:
    xyz customer
    11632 
    US

    Item 2
    Price 100$
    Quantity: 2.0000
    --------------------------------------------------------------------------------

Below is the stored procedure used

CREATE PROCEDURE [dbo].[OrderReport]
AS
DECLARE @Report XML
BEGIN
UPDATE [Orders].dbo.Purchase
SET [Status] = 'Failed', RejectedReason = '<errors><e message="idle state" /></errors>'
WHERE [Status] = 'Not processed' 
SET @Report = (
SELECT 
p.OrderGroupId as "@OrderGroupId", 
p.Name as "Purchase",
p.Status as "@Status",
p.SoldToParty as "@SoldToParty",
(
SELECT u1.u_first_name as "@FirstName",
u1.u_last_name as "@LastName",
u1.u_email_address as "@EmailAddress",
FROM [Users].dbo.User u1
WHERE u1.u_user_id = u.u_user_id
for xml PATH('Users'), type
),
(
SELECT oa.AddressId as "@AddressId",
oa.Name as "@BuzinessName",
oa.Line1 as "@Line1",
oa.CountryCode as "@CountryCode",
FROM [Orders].dbo.Addresses oa
WHERE oa.AddressId = li.ShippingAddressId
for xml PATH('ShippingAddress'), type
),
(
SELECT 
l.Item as "@Item",
l.ProductId as "@ProductId",
l.Quantity as "@Qty",
l.Price as "@Price",
FROM [Orders].dbo.Items l
WHERE l.OrderId = f.OrderId
for xml PATH('Item'), type
)
FROM [Orders].dbo.OrForms f
JOIN [Orders].dbo.Purchase p on f.OrderId = p.OrderId
LEFT JOIN [Users].dbo.Account sa.soldtoparty=p.linkedParty
LEFT JOIN [Users].dbo.User u ON sa.u_user_id = u.u_user_id
FOR XML PATH('Purchase'), TYPE
)

SELECT @Report
 FOR XML PATH('ErrorReport')
END
GO

And the output XML is

<ErrorReport>
  <Purchase OrderID="0263080F-61BC"  SoldToParty="abc_customer" />
    <ShippingAddress BusinessName="abc_customer" Line1="11632" CountryCode="US" />
    <Item ItemNumber="1" ProductId="Clothes" Quantity="1.0000" Price="0.0000" />
  </Purchase>
  <Purchase OrderID="02636567-61BC"  SoldToParty="xyz_customer" />
    <ShippingAddress BusinessName="xyz_customer" Line1="11632" CountryCode="US" />
    <Item ItemNumber="2" ProductId="Grocery" Quantity="2.0000" Price="0.0000" />
  </Purchase>
</ErrorReport>

I thought splitting the XML node into separate rows might trigger separate emails for each error.NOt sure how the line demarcation is coming from.

Answer 1

I have below SQL Stored procedure which emails failed order report

You have? If so, why didn't you show it? The procedure you posted only produces some XML. That makes me think that the code that actually composes and sends the email in the client code. If so, you are barking up the wrong tree altogether,

Answer 2

Hi @Shali_123 ,

Here is a conceptual example for you how to loop through a DB table in T-SQL.

SQL

DECLARE @IDVar INT, @NameVar VARCHAR(50);  
  
DECLARE @tbl TABLE (ID INT, [Name] VARCHAR(50));  
INSERT @tbl (ID, [Name])  
VALUES (1, 'Employee')  
   , (2, 'Department')  
   , (3, 'Class');  
  
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tbl);  
  
WHILE @RowCount > 0 BEGIN  
	SELECT @IDVar=ID, @NameVar=[Name]   
	FROM @tbl   
	ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;  
     
	-- do whatever needed, apply any logic, call stored procedures, etc.  
	/*  
	Exec usp_Employee @name = @NameVar  
	Exec usp_Department @ID = @IDVar,@name = @NameVar  
	*/  
  
   SET @RowCount -= 1;  
END