TSQL Query Inserting data from xPath

I am using SQL Server 2012 and trying trying to insert data into multiple tables from an XML string containing the data. The issue and confusion stems from the XML containing multiple nodes so its not just a single record at a time.

Due to this, I am using the output method to insert the data along with the Identity so I know the result of each of the records it inserts.

My problem is due to the structure of the XML string, it is not inserting all of the data it needs to.

Here is the block of code I am working with along with a SQL Fiddle:

Fiddle: http://ift.tt/1Eawjke

    DECLARE @xml xml = '<root>
   <trainingEventID>572</trainingEventID>
   <segment>
      <segmentDate>03/03/2015</segmentDate>
      <hours>4</hours>
      <details>
         <locale>653</locale>
         <teammates>3</teammates>
         <leaders>4</leaders>
      </details>
      <details>
         <locale>655</locale>
         <teammates>44</teammates>
         <leaders>55</leaders>
      </details>
      <details>
         <locale>657</locale>
         <teammates>55</teammates>
         <leaders>66</leaders>
      </details>
      <trainers>
         <trainer>
            <empID>User12341</empID>
         </trainer>
      </trainers>
   </segment>
   <segment>
      <segmentDate>03/04/2015</segmentDate>
      <hours>4</hours>
      <details>
         <locale>653</locale>
         <teammates>3</teammates>
         <leaders>4</leaders>
      </details>
      <details>
         <locale>655</locale>
         <teammates>44</teammates>
         <leaders>55</leaders>
      </details>
      <details>
         <locale>657</locale>
         <teammates>55</teammates>
         <leaders>66</leaders>
      </details>
      <trainers>
         <trainer>
            <empID>User1234</empID>
         </trainer>
      </trainers>
   </segment>
   <segment>
      <segmentDate>03/13/2015</segmentDate>
      <hours>4</hours>
      <details>
         <locale>653</locale>
         <teammates>3</teammates>
         <leaders>4</leaders>
      </details>
      <details>
         <locale>655</locale>
         <teammates>44</teammates>
         <leaders>55</leaders>
      </details>
      <details>
         <locale>657</locale>
         <teammates>55</teammates>
         <leaders>66</leaders>
      </details>
      <trainers>
         <trainer>
            <empID>User1234</empID>
         </trainer>
      </trainers>
   </segment>
</root>'

-- Declare our temp tables
DECLARE @tmpSeg TABLE (teSegmentID INT, trainingEventID INT, segmentDate DATE, nonProdHrs int);
DECLARE @tmpEvents TABLE (teSegmentID INT IDENTITY(1,1), trainingEventID INT, segmentDate DATE, nonProdHrs INT);

-- First, Insert the main segments
INSERT INTO @tmpEvents(trainingEventID, segmentDate, nonProdHrs)
OUTPUT Inserted.teSegmentID, Inserted.trainingEventID, Inserted.segmentDate, Inserted.nonProdHrs INTO @tmpSeg
SELECT ParamValues.x1.value('../trainingEventID[1]', 'INT'),
       ParamValues.x1.value('(segmentDate/text())[1]', 'DATE'),
       ParamValues.x1.value('(hours/text())[1]', 'INT')
FROM   @xml.nodes('/root/segment') AS ParamValues(x1);

SELECT * FROM @tmpSeg

-- Now, we join on our temp table and insert the Segment Details
SELECT s.teSegmentID,
ParamValues.x1.value('(details/locale/text())[1]', 'INT') AS localeID,
ParamValues.x1.value('(details/teammates/text())[1]', 'INT') AS teammates,
ParamValues.x1.value('(details/leaders/text())[1]', 'INT') AS leaders,
ParamValues.x1.value('(../trainingEventID/text())[1]', 'INT') AS eventID,
ParamValues.x1.value('(segmentDate/text())[1]', 'DATE') AS date,
ParamValues.x1.value('(hours/text())[1]', 'INT') AS hours
FROM @tmpSeg AS s
INNER JOIN @xml.nodes('/root/segment') AS ParamValues(x1)
ON s.trainingEventID = ParamValues.x1.value('(../trainingEventID/text())[1]', 'INT')
AND s.segmentDate = ParamValues.x1.value('(segmentDate/text())[1]', 'DATE')
AND s.nonProdHrs = ParamValues.x1.value('(hours/text())[1]', 'INT')

As you can see from the XML structure, it is broken down into parts. There is a segment and then within the segment there can be multiple Details Nodes.

The first step in the query is to create all of the segments which appears to be working fine. Each segment gets created and the Identity is stored in a temp table from the output.

Next, I need to create records for each details node using the Identity of its parent segment. I do this by joining the temp table from the output some some of its data to get the details needed.

The issue with this is due to multiple details nodes, it is only accessing the first one and storing its data.

The output in the last statement using this example should contain 9 records. There are 3 details nodes for each segment and there are 3 segments total.

Not sure how to accomplish this but its driving me crazy.

Thanks for any help.