sql-server - 将数据从 XML 插入到 SQL Server 中的表中

Question

我有如下所示的 xml。我想插入一个表，我希望插入 2 条记录，但它只插入一条记录。

示例 XML:

<xmlSubmitTime>
  <submitTime>
    <TaskDate>2016-08-29</TaskDate>
    <Project>
      <ProjectId>9</ProjectId>
      <Task>
        <TaskId>5</TaskId>
        <SubTask>
          <SubTaskId>4</SubTaskId>
          <Hours>2</Hours>
          <Comment>sample string 4</Comment>
        </SubTask>
        <SubTask>
          <SubTaskId>5</SubTaskId>
          <Hours>6</Hours>
          <Comment>sample string 4</Comment>
        </SubTask>
      </Task>
    </Project>
  </submitTime>
</xmlSubmitTime>

这是我的插入语句:

INSERT INTO Sample
    SELECT
        submitTime.value('(Project/ProjectId/text())[1]', 'int') ,
        submitTime.value('(Project/Task/TaskId/text())[1]', 'int'),
        submitTime.value('(Project/Task/SubTask/SubTaskId/text())[1]', 'int'),
        submitTime.value('(TaskDate/text())[1]', 'date'),
        submitTime.value('(Project/Task/SubTask/Hours/text())[1]', 'int'),  
        0,
        submitTime.value('(Project/Task/SubTask/Comment/text())[1]', 'varchar(20)'),    
    FROM
        @SaveTimeDate.nodes('/xmlSubmitTime/submitTime') AS TEMPTABLE(submitTime)

预期输出是:

ProjectID  TaskId SubtaskId Hours
----------------------------------
9           5        4        2
9           5        5        6

实际输出为:

ProjectID  TaskId SubtaskId Hours
---------------------------------
 9           5        4        2

Answer 1

您需要再次调用 .nodes()处理多个 <SubTask>您可能拥有的条目。

试试这个:

SELECT
    submitTime.value('(Project/ProjectId)[1]', 'int') ,
    submitTime.value('(Project/Task/TaskId)[1]', 'int'),
    subtask.value('(SubTaskId)[1]', 'int'),
    subtask.value('(Hours)[1]', 'int')
FROM
    @SaveTimeDate.nodes('/xmlSubmitTime/submitTime') AS TEMPTABLE(submitTime)
CROSS APPLY
    submitTime.nodes('Project/Task/SubTask') AS XT(subtask)

这应该会返回您要查找的输出。你需要得到 /Project/Task/SubTask节点也作为 XML 子片段的列表 - 您需要获取 SubTaskId和 Hours从这些子节点中获取所有信息的值