存储过程处理XML：异常生成、错误日志及XML数据入库需求

Alright, let's tackle this problem step by step. You need a stored procedure that handles XML parsing, data insertion, exception handling, and error logging—here's a robust implementation tailored to your requirements:

核心需求拆解 #

• 解析指定的嵌套XML结构（包含Parent和多层Child节点）
• 将数据插入对应数据库表
• 捕获异常并生成自定义错误提示
• 记录详细错误信息用于故障排查

前置假设 #

我们先假设已经创建了三张支撑该流程的表：

1. ParentTable：存储顶层父节点数据（Title, Code, Name, Company）
2. ChildTable：存储关联父节点的子记录（ParentID, Title, ContentType, TimeStarted, TimeFinished）
3. ErrorLogTable：记录错误日志（LogTime, ErrorMessage, ErrorDetails, XMLData）

存储过程实现（SQL Server示例） #

CREATE PROCEDURE dbo.ImportXMLData
    @XMLData XML
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量
    DECLARE @ParentID INT;
    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorDetails NVARCHAR(MAX);

    BEGIN TRY
        -- 开启事务保证数据一致性
        BEGIN TRANSACTION;

        -- 插入父节点数据
        INSERT INTO ParentTable (Title, Code, Name, Company)
        SELECT
            ParentNode.value('(Title)[1]', 'NVARCHAR(100)'),
            ParentNode.value('(Code)[1]', 'NVARCHAR(50)'),
            ParentNode.value('(Name)[1]', 'NVARCHAR(100)'),
            ParentNode.value('(company)[1]', 'NVARCHAR(100)')
        FROM @XMLData.nodes('/Main/Parent') AS T(ParentNode);

        -- 获取刚插入的父节点ID
        SET @ParentID = SCOPE_IDENTITY();

        -- 插入子节点数据并关联父节点
        INSERT INTO ChildTable (ParentID, Title, ContentType, TimeStarted, TimeFinished)
        SELECT
            @ParentID,
            ChildNode.value('(Title)[1]', 'NVARCHAR(100)'),
            ChildNode.value('(ContentType)[1]', 'NVARCHAR(50)'),
            ChildNode.value('(TimeStarted)[1]', 'DATETIME'),
            ChildNode.value('(TimeFinished)[1]', 'DATETIME')
        FROM @XMLData.nodes('/Main/Parent/Children/Child') AS T(ChildNode);

        -- 所有操作成功则提交事务
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 发生错误则回滚事务
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- 捕获错误详情
        SELECT
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorDetails = '错误编号: ' + CAST(ERROR_NUMBER() AS NVARCHAR(20)) + 
                            ', 严重程度: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(20)) +
                            ', 状态码: ' + CAST(ERROR_STATE() AS NVARCHAR(20)) +
                            ', 错误行号: ' + CAST(ERROR_LINE() AS NVARCHAR(20));

        -- 记录错误及原始XML数据用于调试
        INSERT INTO ErrorLogTable (LogTime, ErrorMessage, ErrorDetails, XMLData)
        VALUES (GETDATE(), @ErrorMessage, @ErrorDetails, @XMLData);

        -- 抛出自定义异常通知调用方
        THROW 50001, 'XML数据导入失败，请查看错误日志获取详情。', 1;
    END CATCH
END
GO

关键功能说明 #

• XML解析：使用nodes()拆分XML节点，value()提取指定字段值，确保精准获取每个元素的数据
• 事务安全：将所有插入操作包裹在事务中，一旦出错则回滚所有变更，保证数据一致性
• 异常捕获：TRY...CATCH块捕获所有可能的错误，包括XML格式错误、数据类型不匹配、约束冲突等
• 错误日志：存储错误的完整上下文（含原始XML），方便后续定位和修复问题
• 自定义异常：通过THROW返回清晰的用户友好提示，同时保留内部详细日志

使用示例 #

-- 准备符合格式的XML数据
DECLARE @XML XML = '<Main>
    <Parent>
        <Title>Title1</Title>
        <Code>ABC123</Code>
        <Name>name1</Name>
        <company>test1</company>
        <Children>
            <Child>
                <Title>t1</Title>
                <ContentType>T1</ContentType>
                <TimeStarted>2018-03-01T10:47:46</TimeStarted>
                <TimeFinished>2018-03-01T10:48:08</TimeFinished>
            </Child>
            <Child>
                <Title>t2</Title>
                <ContentType>T2</ContentType>
                <TimeStarted>2018-03-01T10:49:00</TimeStarted>
                <TimeFinished>2018-03-01T10:50:00</TimeFinished>
            </Child>
        </Children>
    </Parent>
</Main>';

-- 调用存储过程执行导入
EXEC dbo.ImportXMLData @XMLData = @XML;

注意事项 #

• 大小写敏感：XML区分大小写，确保存储过程中value()方法里的节点名称与XML实际节点完全匹配（比如company和Company的差异）
• 数据类型匹配：value()方法指定的数据类型要与表字段类型一致，避免转换错误
• 多父节点场景：如果XML可能包含多个Parent节点，需要修改存储过程逻辑，循环处理每个父节点及其对应的子节点
• 日志扩展：可以根据需求给ErrorLogTable添加操作人、来源系统等字段，获取更多故障排查上下文

内容的提问来源于stack exchange，提问作者vibs