You need to enable JavaScript to run this app. 如何修改SQL存储过程实现每条错误对应独立XML报告输出
优惠活动
大模型
产品
解决方案
定价
更多

如何修改SQL存储过程实现每条错误对应独立XML报告输出

解决方案

核心思路

原来的逻辑是将所有符合条件的采购订单聚合到同一个<Report>根节点下,要实现单订单对应单份独立报告,只需将聚合生成XML的逻辑从「全量匹配行一次性生成」改为「逐行匹配单条订单单独生成」即可,同时保留原有查询的字段映射和关联逻辑不变。

修改后的存储过程代码

DECLARE @Report XML
-- 声明表变量存储待处理的订单主键,避免处理过程中数据变动
DECLARE @PendingOrders TABLE (
    GroupId INT, -- 对应PurOrd和OrForms的关联主键,可根据实际表结构调整类型
    OrderFormId INT
)

BEGIN
    -- 原有更新订单状态逻辑不变
    UPDATE [Orders].dbo.PurOrd
    SET [Status] = 'Failed', Reason = '<e id="0" message="failed test order" />'
    WHERE [Status] = 'InProcess' 

    -- 先把所有待生成报告的订单存入表变量
    INSERT INTO @PendingOrders (GroupId, OrderFormId)
    SELECT p.GroupId, f.OrderFormId
    FROM [Orders].dbo.OrForms f
    JOIN [Orders].dbo.PurOrd p on f.GroupId = p.GroupId
    WHERE p.Status = 'OrderRejected' AND p.ReportStatus IS NULL

    -- 方案1:直接输出所有独立Report节点(适合直接导出结果)
    SELECT 
        (
            SELECT 
                p.Name as "@Name",
                p.Customer "@Customer",
                CASE p.Name
                    WHEN 'Default' THEN convert(xml,p.RejectedReason)
                    ELSE convert(xml,f.RejectedReason)
                END AS "RejectedReason",
                (
                    SELECT u.first_name as "@FirstName",
                    u.last_name as "@LastName"
                    FROM [Users].dbo.Users u
                    WHERE u.user_id = u.user_id -- 此处原逻辑保留,建议修改为订单关联用户的过滤条件
                    for xml PATH('Users'), type
                ),
                (
                    SELECT
                    li.Product as "@PId",
                    li.Quantity as "@Quantity",
                    li.SalePrice as "@Price"
                    FROM [Cart].dbo.LineItems li
                    WHERE li.OrderFormId = f.OrderFormId
                    ORDER BY li.ItemNumber
                    for xml PATH('LineItem'), type
                )
            FROM [Orders].dbo.OrForms f
            JOIN [Orders].dbo.PurOrd p on f.GroupId = p.GroupId
            WHERE p.GroupId = po.GroupId AND f.OrderFormId = po.OrderFormId
            FOR XML PATH('PurchaseOrder'), TYPE -- 与示例输出的PurchaseOrder标签对齐
        ) AS [node()]
    FROM @PendingOrders po
    FOR XML PATH('Report'), TYPE -- 每个订单外层单独套Report根节点

    -- 方案2:循环逐单生成报告(适合需要逐单发邮件的场景,可在循环内加入发邮件逻辑)
    /*
    DECLARE @CurGroupId INT, @CurOrderFormId INT
    DECLARE order_cursor CURSOR FOR 
    SELECT GroupId, OrderFormId FROM @PendingOrders

    OPEN order_cursor
    FETCH NEXT FROM order_cursor INTO @CurGroupId, @CurOrderFormId

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Report = (
            SELECT 
                p.Name as "@Name",
                p.Customer "@Customer",
                CASE p.Name
                    WHEN 'Default' THEN convert(xml,p.RejectedReason)
                    ELSE convert(xml,f.RejectedReason)
                END AS "RejectedReason",
                (
                    SELECT u.first_name as "@FirstName",
                    u.last_name as "@LastName"
                    FROM [Users].dbo.Users u
                    WHERE u.user_id = u.user_id
                    for xml PATH('Users'), type
                ),
                (
                    SELECT
                    li.Product as "@PId",
                    li.Quantity as "@Quantity",
                    li.SalePrice as "@Price"
                    FROM [Cart].dbo.LineItems li
                    WHERE li.OrderFormId = f.OrderFormId
                    ORDER BY li.ItemNumber
                    for xml PATH('LineItem'), type
                )
            FROM [Orders].dbo.OrForms f
            JOIN [Orders].dbo.PurOrd p on f.GroupId = p.GroupId
            WHERE p.GroupId = @CurGroupId AND f.OrderFormId = @CurOrderFormId
            FOR XML PATH('PurchaseOrder'), ROOT('Report'), TYPE
        )
        
        -- 此处可加入调用邮件发送存储过程的逻辑,传入当前@Report作为附件/内容
        -- SELECT @Report -- 测试输出当前订单的报告

        -- 标记当前订单报告已生成,避免重复处理
        UPDATE [Orders].dbo.PurOrd 
        SET ReportStatus = 1 
        WHERE GroupId = @CurGroupId

        FETCH NEXT FROM order_cursor INTO @CurGroupId, @CurOrderFormId
    END

    CLOSE order_cursor
    DEALLOCATE order_cursor
    */
END

关键修改说明

  • 新增@PendingOrders表变量预存待处理订单,避免处理过程中数据变动导致的不一致问题
  • 原有字段映射、表关联逻辑完全保留,仅调整XML生成的层级:给每个订单的PurchaseOrder节点单独套一层Report根节点
  • 提供两种实现方案:直接批量输出所有独立报告适合导出场景,游标循环逐单生成适合需要单独发邮件的场景
  • 补充了报告生成后标记ReportStatus的逻辑,避免重复生成报告
  • 修正了原代码中子查询尾部多余的逗号语法问题

内容的提问来源于stack exchange,提问作者ish_dev

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.21 16:45:04