我有以下类似的xml
。我想要将其插入到一个表中,并期望插入两条记录,但实际上只插入了一条记录。
示例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