从XML向父子表插入数据时重复记录问题求助

你的问题核心在于：当XML中存在多个user和tool字段完全相同的task节点时，插入子表TaskRole时无法精准关联到对应的父表Task记录——因为仅靠RequestID、ToolID和QID无法唯一标识单个父任务，导致每个子角色记录会匹配所有符合条件的父任务，最终产生重复数据。

解决方案思路 #

要解决这个问题，我们需要给XML中的每个task节点分配一个唯一的临时标识，在插入父表时保留这个标识，然后通过它将父表生成的TaskID与对应的子角色记录精准关联。具体步骤如下：

1. 提取XML数据时，给每个task节点添加唯一行号；
2. 插入父表Task时，用OUTPUT子句将生成的TaskID与临时行号绑定；
3. 插入子表TaskRole时，通过临时行号关联到对应的TaskID，确保一一对应。

完整代码示例 #

DECLARE @tasks XML = '<request>
  <task>
    <user>Q500</user>
    <tool>31</tool>
    <role>
      <roleID>w1234</roleID>
      <action>2</action>
    </role>
  </task>
  <task>
    <user>Q500</user>
    <tool>31</tool>
    <role>
      <roleID>w123456</roleID>
      <action>1</action>
    </role>
  </task>
</request>';

DECLARE @requestID INT = 123; -- 替换为实际的RequestID

-- 步骤1：将XML数据提取到临时表，并添加唯一行号
DECLARE @TaskStaging TABLE (
    TaskRowNum INT IDENTITY(1,1) PRIMARY KEY, -- 唯一临时标识
    UserName VARCHAR(10),
    ToolID INT,
    RoleID VARCHAR(10),
    ActionID INT
);

INSERT INTO @TaskStaging (UserName, ToolID, RoleID, ActionID)
SELECT
    x1.value('user[1]', 'VARCHAR(10)'),
    x1.value('tool[1]', 'INT'),
    x1.value('role/roleID[1]', 'VARCHAR(10)'),
    x1.value('role/action[1]', 'INT')
FROM @tasks.nodes('/request/task') AS ParamValues(x1);

-- 步骤2：插入父表Task，同时捕获生成的TaskID和对应的临时行号
DECLARE @InsertedTasks TABLE (TaskID INT, TaskRowNum INT);

INSERT INTO Task (RequestID, ToolID, QID)
OUTPUT inserted.TaskID, s.TaskRowNum INTO @InsertedTasks
SELECT @requestID, s.ToolID, s.UserName
FROM @TaskStaging s;

-- 步骤3：插入子表TaskRole，通过临时行号精准关联父任务
INSERT INTO TaskRole (TaskID, RoleID, ActionID)
SELECT it.TaskID, s.RoleID, s.ActionID
FROM @TaskStaging s
INNER JOIN @InsertedTasks it ON s.TaskRowNum = it.TaskRowNum;

关键细节说明 #

• 临时行号TaskRowNum：这个字段给每个XML中的task节点分配了唯一标识，即使user和tool相同，也能区分不同的任务。
• OUTPUT子句：在插入父表时，我们用它将自动生成的TaskID和临时行号绑定到@InsertedTasks表中，建立了父任务和临时标识的映射关系。
• 精准关联：最后插入子表时，通过TaskRowNum进行关联，确保每个子角色记录只匹配对应的父任务，彻底避免了重复匹配的问题。

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