从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值以获取所有信息