TSQL查询从xPath插入数据

Question

我正在使用SQL Server 2012，并尝试从包含数据的XML字符串中将数据插入到多个表中。问题和混乱源于包含多个节点的XML，因此它一次不只是一条记录。

因此，我使用output方法将数据与Identity一起插入，这样我就可以知道它插入的每个记录的结果。

我的问题是由于XML字符串的结构，它没有插入所需的所有数据

以下是我正在使用的代码块以及SQL Fiddle:

Fiddle：http://sqlfiddle.com/#！6/d41d8/24236

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

从XML结构中可以看出，它被分解为多个部分。存在一个segment，然后在该段内可以存在多个Details节点。

查询的第一步是创建所有运行良好的段。创建每个段，并将Identity存储在输出的临时表中。

接下来，我需要使用其父分段的标识为每个细节节点创建记录。我通过将输出中的temp表中的一些数据连接起来来获得所需的详细信息。

这个问题是由于多个details节点，它只访问第一个节点并存储其数据。

使用此示例的最后一条语句中的输出应该包含9条记录。每个分段有3个details节点，总共有3个分段。

不知道如何做到这一点，但这让我抓狂。

谢谢你的帮助。

Answer 1

首先，您需要另一个级别的详细信息，但还有培训师的问题。。我对这里的解决方案有点随意，所以可以根据需要进行修改。

    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 temp tables
DECLARE @tmpSeg TABLE (teSegmentID INT IDENTITY(1,1), trainingEventID INT, segmentDate DATE, nonProdHrs INT, trainer varchar(30));
DECLARE @tmpLocales TABLE (teSegmentID INT, trainingEventID INT/*, segmentDate DATE, nonProdHrs int*/, locale int, teammates int, leaders int);
DECLARE @tmpTrainers TABLE (teSegmentID INT, trainingEventID INT, empID VARCHAR(30));
-- Get Segment info
INSERT INTO @tmpSeg(trainingEventID, segmentDate, nonProdHrs, trainer)
SELECT 
      ParamValues.x1.value('../trainingEventID[1]', 'INT')
    , ParamValues.x1.value('segmentDate[1]', 'DATE')
    , ParamValues.x1.value('hours[1]', 'INT')
    , ParamValues.x1.value('trainers[1]/trainer[1]/empID[1]', 'VARCHAR(30)')
FROM   @xml.nodes('/root/segment') AS ParamValues(x1);
SELECT * FROM @tmpSeg
-- Get Segment-dependent trainer info
INSERT INTO @tmpTrainers(teSegmentID, trainingEventID, empID)
SELECT
        S.teSegmentID
      , D.trainingEventID
      , D.empID
FROM (
    SELECT
          ParamValues.x1.value('empID[1]', 'VARCHAR(30)') AS empID
        , ParamValues.x1.value('../../../trainingEventID[1]', 'INT') AS trainingEventID
        , ParamValues.x1.value('../../segmentDate[1]', 'DATE') AS segmentDate
        , ParamValues.x1.value('../../hours[1]', 'INT') AS nonProdHours
    FROM @xml.nodes('/root/segment/trainers/trainer') AS ParamValues(x1)
    ) D
    INNER JOIN @tmpSeg S ON D.trainingEventID = S.trainingEventID
        AND D.segmentDate = S.segmentDate
        AND D.nonProdHours = S.nonProdHrs
SELECT * FROM @tmpTrainers
-- Get segment-dependent locale info
INSERT INTO @tmpLocales
SELECT
        S.teSegmentID
      , D.trainingEventID
      , D.locale
      , D.teammates
      , D.leaders
FROM (
    SELECT
          ParamValues.x1.value('locale[1]', 'INT') AS locale
        , ParamValues.x1.value('teammates[1]', 'INT') AS teammates
        , ParamValues.x1.value('leaders[1]', 'INT') AS leaders
        , ParamValues.x1.value('../../trainingEventID[1]', 'INT') AS trainingEventID
        , ParamValues.x1.value('../segmentDate[1]', 'DATE') AS segmentDate
        , ParamValues.x1.value('../hours[1]', 'INT') AS nonProdHours
    FROM @xml.nodes('/root/segment/details') AS ParamValues(x1)
    ) D
    INNER JOIN @tmpSeg S ON D.trainingEventID = S.trainingEventID
        AND D.segmentDate = S.segmentDate
        AND D.nonProdHours = S.nonProdHrs
SELECT *
FROM @tmpLocales

Answer 2

您的最后一个SELECT仍在<segment>节点中迭代，其中只有3个节点。您需要使用另一个CROSS APPLY:将其降低到<details>级别

-- Now, we join on our temp table and insert the Segment Details
SELECT s.teSegmentID,
D.Detail.value('locale[1]', 'INT') AS localeID,
D.Detail.value('teammates[1]', 'INT') AS teammates,
D.Detail.value('leaders[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)
CROSS APPLY ParamValues.x1.nodes('details') AS D(Detail)
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')