SQL - Inserting records from XML string into multiple tables

Question

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.

XML Structure:

<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>

Insert the Parent (task):

-- 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);

Insert the Child (role):

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)')

The issue with the above code is that the user and tool can be the same across multiple tasks so when it comes time to join, I am getting a lot of duplicate entries.

In this case, the child query is looking for a parent task that has a ToolID=31 and User=Q500. Since there are two, its inserting two records which is incorrect, there should only be one child for each of those parents.

How else can I go about accomplishing this since I don't really have anything unique to join on?

Update 1 (Task Table Structure):

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]

Update 2 - Example Code:

Here is an example of what I am trying to do. The parent (tasks) are inserted on the first pass so that a AI/PK is created for them. I then need to insert the child records (roles) into another table, referencing the task that it belongs to.

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

Answer 1

Maybe following SQL XML Query with Cross Apply could help to fetch the raw data, then it could be inserted into parent-child tables

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>'

SELECT
    ROW_NUMBER() over (order by getdate()) as rn,
    ParamValues.x1.value('tool[1]', 'INT') as tool,
    ParamValues.x1.value('user[1]', 'VARCHAR(10)') as [user],
    r1.value('roleID[1]', 'VARCHAR(10)') as roleid,
    r1.value('action[1]', 'VARCHAR(10)') as [action]
FROM @tasks.nodes('/request/task') AS ParamValues(x1)
Cross Apply ParamValues.x1.nodes('./role') AS Roles(r1)

The output of the above SQL Select will be as follows

Answer 2

So the problem is that you need the Identity TaskID from Task to be associated with the right roles, but you don't have anything in your source data that's unique to associate back with the row inserted into Task. You could use a cursor to insert into Task and output the identity column for each row and then insert into TaskRole, but that would be terribly inefficient.

Fortunately, a trick with MERGE is that you can OUTPUT not only the inserted columns (TaskID) but the source columns as well - even unused ones such as RoleID and ActionID. So you can insert into Task using MERGE as part of an inner query and use the output to insert into TaskRole.

Please note the ON 1 = 0. That basically forces the MERGE to act like an INSERT.

INSERT INTO TaskRole (TaskID, RoleID, ActionID)
SELECT TaskID, RoleID, ActionID
FROM (
    MERGE INTO Task
    USING (
        SELECT pv.item.value('../user[1]', 'VARCHAR(10)') AS QID
            , pv.item.value('../tool[1]', 'INT') AS ToolID
            , pv.item.value('roleID[1]', 'varchar(10)') RoleID
            , pv.item.value('action[1]', 'INT') ActionID
        FROM @tasks.nodes('/request/task/role') pv(item)
    ) AS src
    ON 1 = 0
    WHEN NOT MATCHED THEN INSERT (RequestID, ToolID, QID) VALUES (@RequestID, ToolID, QID)
    OUTPUT Inserted.TaskID, src.RoleID, src.ActionID
) src;