基于 xpath 的 SQL 插入数据

Question

我有一个简单的插入语句，我正在处理。此查询插入核心记录，返回 ID，然后将一个或多个任务插入到另一个表中。对于这些任务中的每一个，我都需要在第三个表中执行额外的插入操作。

由于第二个插入语句发生在第三个插入语句之前，我在尝试弄清楚如何设置它时遇到了一点麻烦，所以我不确定如何从每个插入中获取identity以传递给查询 3。

DECLARE @requestID INT;
    -- Insert the core request details
    INSERT INTO esas.Request (Requestor, Justification, CreatedBy, DateCreated)
    SELECT @requestor,
           @justification,
           @creator,
           GETUTCDATE()
    -- Define the core request ID
    SET @requestID = SCOPE_IDENTITY();
    -- Add tasks
    INSERT INTO esas.Task
            ( RequestID ,
              ToolID ,
              QID ,
              Action
            )
     SELECT  @requestID,
             ParamValues.x1.value('tool[1]', 'INT'),
             ParamValues.x1.value('user[1]', 'VARCHAR(10)'),
             ParamValues.x1.value('action[1]', 'INT)')
     FROM @tasks.nodes('/request/task') AS ParamValues(x1);
     -- For each task, add any associated roles (stuck here)
     INSERT INTO esas.TaskRoles
                ( TaskID, 
                  RoleID, 
                  ActionID )
     VALUES  ( 0, -- TaskID - int
               0, -- RoleID - int
               0  -- ActionID - int
               )

在最后一个插入中，我需要将从"添加任务"创建的TaskID PK/AI传递到另一个表，以及 XML 字符串中的roleID和action。

这是我的 XML 结构：

<request>
    <task>
        <tool>123</tool>
        <user>4567</user>
        <roles>
            <role>
                <roleID>12</roleID>
                <action>1</action>
            </role>
            <role>
                <roleID>1245</roleID>
                <action>0</action>
            </role>
            <role>
                <roleID>678</roleID>
                <action>1</action>
            </role>
        </roles>
    </task>
</request>

我的困惑是由于知道INSERT INTO esas.Task在继续之前会立即发生，所以我不确定如何将每个标识及其在 XML 结构中的相应详细信息传递给下一个插入。

Answer 1

我使用 xpath 并在插入数据后连接 tasks 表

来解决这个问题。

     -- For each task, add any associated roles
     INSERT INTO esas.TaskRoles
                ( TaskID, 
                  RoleID, 
                  ActionID )
     SELECT  t.TaskID,
             ParamValues.x1.value('roleID[1]', 'INT'),
             ParamValues.x1.value('action[1]', 'INT')
     FROM esas.Task AS t
     JOIN @tasks.nodes('/request/task/roles/role') AS ParamValues(x1)
     ON t.RequestID = @requestID
     AND t.ToolID =  ParamValues.x1.value('../../tool[1]', 'INT')
     AND t.QID =  ParamValues.x1.value('../../user[1]', 'VARCHAR(10)')