title: SQL - Inserting records from XML string into multiple tables
body: <p>I have a parent and child table that I am needing to insert data into. For each parent task that is created, a child task needs to be added, referencing the parent ID.</p>
  
  <p><strong>XML Structure:</strong></p>
  
  <pre><code>&lt;request&gt;
     &lt;task&gt;
        &lt;user&gt;Q500&lt;/user&gt;
        &lt;tool&gt;31&lt;/tool&gt;
        &lt;role&gt;
           &lt;roleID&gt;w1234&lt;/roleID&gt;
           &lt;action&gt;2&lt;/action&gt;
        &lt;/role&gt;
     &lt;/task&gt;
     &lt;task&gt;
        &lt;user&gt;Q500&lt;/user&gt;
        &lt;tool&gt;31&lt;/tool&gt;
        &lt;role&gt;
           &lt;roleID&gt;w123456&lt;/roleID&gt;
           &lt;action&gt;1&lt;/action&gt;
        &lt;/role&gt;
     &lt;/task&gt;
  &lt;/request&gt;
  </code></pre>
  
  <p><strong>Insert the Parent (task):</strong></p>
  
  <pre><code>-- Add tasks
  INSERT INTO Task
          ( RequestID ,
            ToolID ,
            QID 
          )
   SELECT  @requestID,
           ParamValues.x1.value('tool[1]', 'INT'),
           ParamValues.x1.value('user[1]', 'VARCHAR(10)')
   FROM @tasks.nodes('/request/task') AS ParamValues(x1);
  </code></pre>
  
  <p><strong>Insert the Child (role):</strong></p>
  
  <pre><code>INSERT INTO TaskRole
              ( TaskID, 
                RoleID, 
                ActionID )
   SELECT  t.TaskID,
           ParamValues.x1.value('roleID[1]', 'varchar(10)'),
           ParamValues.x1.value('action[1]', 'INT')
   FROM Task AS t
   JOIN @tasks.nodes('/request/task/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)')
  </code></pre>
  
  <p>The issue with the above code is that the <code>user</code> and <code>tool</code> can be the same across multiple tasks so when it comes time to join, I am getting a lot of duplicate entries.</p>
  
  <p>In this case, the child query is looking for a parent task that has a <code>ToolID=31</code> and <code>User=Q500</code>. Since there are two, its inserting two records which is incorrect, there should only be one child for each of those parents.</p>
  
  <p>How else can I go about accomplishing this since I don't really have anything unique to join on?</p>
  
  <p><strong>Update 1 (Task Table Structure):</strong></p>
  
  <pre><code>CREATE TABLE [Task](
      [TaskID] [INT] IDENTITY(1,1) NOT NULL,
      [RequestID] [INT] NOT NULL,
      [ToolID] [INT] NOT NULL,
      [QID] [VARCHAR](10) NOT NULL,
      [StatusID] [INT] NOT NULL CONSTRAINT [DF_Task_StatusID]  DEFAULT ((1)),
      [TaskOwner] [VARCHAR](10) NULL,
   CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
  (
      [TaskID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  CREATE TABLE [TaskRole](
      [TaskID] [INT] NOT NULL,
      [RoleID] [VARCHAR](10) NOT NULL,
      [ActionID] [INT] NOT NULL,
   CONSTRAINT [PK_TaskRoles] PRIMARY KEY CLUSTERED 
  (
      [TaskID] ASC,
      [RoleID] ASC,
      [ActionID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p><strong>Update 2 - Example Code:</strong></p>
  
  <p>Here is an example of what I am trying to do. The parent (tasks) are inserted on the first pass so that a <code>AI/PK</code> is created for them. I then need to insert the child records (roles) into another table, referencing the task that it belongs to.</p>
  
  <pre><code>DECLARE @parent TABLE (requestID INT IDENTITY,qid varchar(10), tool int)
  DECLARE @child TABLE  (taskID INT IDENTITY, requestID INT, roleID VARCHAR(20), actionID int)
  
  INSERT INTO @parent( qid, tool ) VALUES  ( 'Q500', 31) -- Task 1
  INSERT INTO @parent( qid, tool ) VALUES  ( 'Q500', 31) -- Task 2
  
  INSERT INTO @child( requestID, roleID, actionID ) VALUES  (1, 'w1234', 2) -- Role for Task 1
  INSERT INTO @child( requestID, roleID, actionID ) VALUES  (1, 'w123456', 1) -- Role for Task 2
  
  SELECT * 
  FROM @parent AS p
  JOIN @child AS c
  ON p.requestID = c.requestID
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/0qXOi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0qXOi.png" alt="enter image description here"></a></p>