TSQL Inserting records from XML string

Question

I have a SQL query that is inserting records into a table from an XML string that I pass to it. The string could contain 1 node or multiple so each one is a new record.

Here is my XML string:

<root>
    <data>
      <segment>
         <trainingEventID>9</trainingEventID>
         <localeID>641</localeID>
         <numOfTeammates>12</numOfTeammates>
         <nonProdHrs>21</nonProdHrs>
         <segmentDate>10/10/2014</segmentDate>
         <trainers>
            <trainer>
               <empID>HUS123</empID>
            </trainer>
            <trainer>
               <empID>Dan123</empID>
            </trainer>
         </trainers>
      </segment>
   </data>
   <data>
      <segment>
         <trainingEventID>9</trainingEventID>
         <localeID>641</localeID>
         <numOfTeammates>12</numOfTeammates>
         <nonProdHrs>21</nonProdHrs>
         <segmentDate>10/25/2014</segmentDate>
         <trainers>
            <trainer>
               <empID>HUS123</empID>
            </trainer>
            <trainer>
               <empID>Dan123</empID>
            </trainer>
         </trainers>
      </segment>
   </data>
</root>

Every segment is a new record that is added into the table.

Now, I have a separate table called trainers. For each trainer, I need to also insert a record into that table but it needs to have the last inserted record id of the segment.

Here is my query:

 INSERT INTO myTable(trainingEventID, localeID, segmentDate, numofTeammates, nonProdHrs)
    SELECT ParamValues.x1.value('trainingEventID[1]', 'INT'),
           ParamValues.x1.value('localeID[1]', 'INT'),
           ParamValues.x1.value('segmentDate[1]', 'DATE'),
           ParamValues.x1.value('numOfTeammates[1]', 'INT'),
           ParamValues.x1.value('nonProdHrs[1]', 'FLOAT')
    FROM   @xml.nodes('/root/data/segment') AS ParamValues(x1);

How can I go about inserting the trainers into another table with the record ID that was created from the segment insert?

What is the key to each segment? `trainingEventID` + `segmentDate`? — Code Different, Oct 30 '14 at 00:34
This seems nearly identical to the answer I provided here ( http://stackoverflow.com/questions/26124743/bulk-insert-nested-xml-with-foreign-key-as-identity-column-of-first-table/26128718#26128718 ) except the "last inserted record id of the segment" seems odd. Do you mean that if the `empID` value is present in multiple Segments, you want only the most recent Segment (based on `segmentDate`)? Or did you want each individual `segmentDate` for that `empID`? — Solomon Rutzky, Oct 30 '14 at 03:21
So in the example above we would have 2 segment nodes (2 records) that are inserted into the table. But with an auto increment ID for the record. Now, I need to add the trainers into another table but they need to reference the segment is that was just inserted. There would be a total of 4 records inserted into the trainer table, 2 have the segment id of 1 and the other 2 with the segment id of 2. — SBB, Oct 30 '14 at 12:33
From this, I could then say select * from trainers where segmentId=1 and it would show me both trainers — SBB, Oct 30 '14 at 12:34

score 1 · Accepted Answer · answered Oct 30 '14 at 15:00

Given the clarification of this statement in the question:

For each trainer, I need to also insert a record into that table but it needs to have the last inserted record id of the segment.

being (as found in the comments on the question):

There would be a total of 4 records inserted into the trainer table, 2 have the segment id of 1 and the other 2 with the segment id of 2.

The following will insert this data into related tables that have auto-incrementing IDs. In the sample data, I varied the EmpID values slightly to make it clearer that it is indeed working as expected.

DECLARE @DocumentID INT, @ImportData XML;

SET @ImportData = N'
<root>
    <data>
      <segment>
         <trainingEventID>9</trainingEventID>
         <localeID>641</localeID>
         <numOfTeammates>12</numOfTeammates>
         <nonProdHrs>21</nonProdHrs>
         <segmentDate>10/10/2014</segmentDate>
         <trainers>
            <trainer>
               <empID>HUS123</empID>
            </trainer>
            <trainer>
               <empID>Dan123</empID>
            </trainer>
         </trainers>
      </segment>
    </data>
    <data>
      <segment>
         <trainingEventID>9</trainingEventID>
         <localeID>641</localeID>
         <numOfTeammates>12</numOfTeammates>
         <nonProdHrs>21</nonProdHrs>
         <segmentDate>10/25/2014</segmentDate>
         <trainers>
            <trainer>
               <empID>HUS1234</empID>
            </trainer>
            <trainer>
               <empID>Dan1234</empID>
            </trainer>
         </trainers>
      </segment>
   </data>
</root>';


DECLARE @Segment TABLE (SegmentId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
                        TrainingEventID INT NOT NULL, -- Unique
                        LocaleID INT NOT NULL, -- Unique
                        NumOfTeammates INT,
                        NonProdHrs INT,
                        SegmentDate DATE); -- Unique
-- Ideally create UNIQUE INDEX with the 3 fields noted above
DECLARE @Trainer TABLE (TrainerId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
                        SegmentID INT NOT NULL, -- FK to Segment.SegmentID
                        EmpID VARCHAR(50) NOT NULL);

EXEC sp_xml_preparedocument @DocumentID OUTPUT, @ImportData;

-- First pass: extract "Segment" rows
INSERT INTO @Segment
            (TrainingEventID, LocaleID, NumOfTeammates, NonProdHrs, SegmentDate)
   SELECT TrainingEventID, LocaleID, NumOfTeammates, NonProdHrs, SegmentDate
   FROM   OPENXML (@DocumentID, N'/root/data/segment', 2) 
             WITH (TrainingEventID   INT  './trainingEventID/text()', 
                   LocaleID          INT  './localeID/text()',
                   NumOfTeammates    INT  './numOfTeammates/text()',
                   NonProdHrs        INT  './nonProdHrs/text()',
                   SegmentDate       DATE './segmentDate/text()');


-- Second pass: extract "Trainer" rows
INSERT INTO @Trainer (SegmentID, EmpID)
   SELECT seg.SegmentID, trnr.EmpID
   FROM   OPENXML (@DocumentID, N'/root/data/segment/trainers/trainer', 2) 
             WITH (TrainingEventID   INT         '../../trainingEventID/text()',
                   LocaleID          INT         '../../localeID/text()',
                   SegmentDate       DATE        '../../segmentDate/text()',
                   EmpID             VARCHAR(50) './empID/text()') trnr
   INNER JOIN @Segment seg
           ON seg.[TrainingEventID] = trnr.[TrainingEventID]
          AND seg.[LocaleID] = trnr.[LocaleID]
          AND seg.[SegmentDate] = trnr.[SegmentDate];


EXEC sp_xml_removedocument @DocumentID;
-------------------

SELECT * FROM @Segment ORDER BY [SegmentID];
SELECT * FROM @Trainer ORDER BY [SegmentID];

Output:

SegmentId   TrainingEventID   LocaleID   NumOfTeammates   NonProdHrs   SegmentDate
1           9                 641        12               21           2014-10-10
2           9                 641        12               21           2014-10-25

TrainerId   SegmentID   EmpID
1           1           HUS123
2           1           Dan123
3           2           HUS1234
4           2           Dan1234

References:

I'm trying this out but I get the error `Procedure or function 'BS_Create_TrainingSegment' expects parameter '@DocumentID', which was not supplied. ` What should I be supplying as a `@DocumentID`? — SBB, Oct 30 '14 at 15:51
@SBB `@DocumentID` is not an input param. It is declared locally and populated as an `OUTPUT` param on `EXEC sp_xml_preparedocument`. — Solomon Rutzky, Oct 30 '14 at 15:56

TSQL Inserting records from XML string

1 Answers1

Linked