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

Not sure what your question is here. It seems that your parent table is missing a critical element, a primary key. And then you are inserting duplicates so of course you can't figure out which parent is which. You have no way of isolating a single row. Perhaps you should share your table structure here and we will see what we can do to help. — Sean Lange
– Sean Lange, Commented Jan 31, 2018 at 15:51
@SeanLange - Added the table structure. Parent has the appropriate key in this case. The tasks (parent) are inserted just fine and all contain their AI PK. When it comes time to insert the role (child) for each of those tasks, I need to associate the child with the parent but don't have anything unique to join on in order to get the data I need. — SBB
– SBB, Commented Jan 31, 2018 at 15:55

Eralper · Accepted Answer · 2018-01-31 16:36:54Z

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

Daniel Gimenez · Accepted Answer · 2018-01-31 16:31:23Z

0

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;

edited Jan 31, 2018 at 16:31

answered Jan 31, 2018 at 15:56

Daniel Gimenez

21.1k4 gold badges60 silver badges78 bronze badges

1 Comment

SBB Over a year ago

There are no duplicates on the parent level. In my example, I fully expect two parent records to be created since their children are what is unique about them. Its the children that I need to associate with the taskID when inserting them.

Collectives™ on Stack Overflow

SQL - Inserting records from XML string into multiple tables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related