求完善SQL Server XML存储过程：验证并插入缺失数据

假设你的数据库存在以下表结构（如果实际表结构不同，需对应调整字段名和约束）：

• Countries：存储国家信息，包含自增主键CountryID和唯一约束的CountryName
• Cities：存储城市信息，包含自增主键CityID、城市名CityName、关联国家的CountryID，且CityName+CountryID组合设为唯一约束
• Buyers：存储买家信息，包含自增主键BuyerID、姓名FirstName、姓氏LastName、唯一约束的Email、电话Phone、关联城市的CityID

以下是完整的存储过程实现：

CREATE PROCEDURE dbo.ImportBuyerFromXML
    @InputXML XML
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 从XML中提取所有需要的字段
    DECLARE 
        @FirstName NVARCHAR(50) = @InputXML.value('(/Buyer/@N)[1]', 'NVARCHAR(50)'),
        @LastName NVARCHAR(50) = @InputXML.value('(/Buyer/@S)[1]', 'NVARCHAR(50)'),
        @CityName NVARCHAR(100) = @InputXML.value('(/Buyer/@CTY)[1]', 'NVARCHAR(100)'),
        @CountryName NVARCHAR(100) = @InputXML.value('(/Buyer/@CTR)[1]', 'NVARCHAR(100)'),
        @Email NVARCHAR(100) = @InputXML.value('(/Buyer/E)[1]', 'NVARCHAR(100)'),
        @Phone NVARCHAR(20) = @InputXML.value('(/Buyer/T)[1]', 'NVARCHAR(20)'),
        @CountryID INT,
        @CityID INT;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 2. 处理国家：不存在则插入，获取国家ID
        IF NOT EXISTS (SELECT 1 FROM Countries WHERE CountryName = @CountryName)
        BEGIN
            INSERT INTO Countries (CountryName)
            VALUES (@CountryName);
            SET @CountryID = SCOPE_IDENTITY();
        END
        ELSE
        BEGIN
            SELECT @CountryID = CountryID FROM Countries WHERE CountryName = @CountryName;
        END

        -- 3. 处理城市：按城市名+国家ID组合判断，不存在则插入，获取城市ID
        IF NOT EXISTS (SELECT 1 FROM Cities WHERE CityName = @CityName AND CountryID = @CountryID)
        BEGIN
            INSERT INTO Cities (CityName, CountryID)
            VALUES (@CityName, @CountryID);
            SET @CityID = SCOPE_IDENTITY();
        END
        ELSE
        BEGIN
            SELECT @CityID = CityID FROM Cities WHERE CityName = @CityName AND CountryID = @CountryID;
        END

        -- 4. 处理买家：按邮箱判断，不存在则插入
        IF NOT EXISTS (SELECT 1 FROM Buyers WHERE Email = @Email)
        BEGIN
            INSERT INTO Buyers (FirstName, LastName, Email, Phone, CityID)
            VALUES (@FirstName, @LastName, @Email, @Phone, @CityID);
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 出错时回滚事务并抛出错误
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END

关键逻辑说明 #

• XML解析：使用value()方法通过XQuery路径提取XML中的属性和节点值
• 存在性校验：通过IF NOT EXISTS()避免重复插入数据，确保数据唯一性
• 事务控制：用TRY/CATCH包裹事务，保证国家、城市、买家的操作要么全部成功，要么全部回滚，避免数据不一致
• 关联ID获取：使用SCOPE_IDENTITY()获取刚插入的自增主键，确保表之间的关联关系正确

内容的提问来源于stack exchange，提问作者dom-gg