Insert data into table in SQL Server from XML

Question

I have xml like below. I want to insert in a table and I am expecting to insert 2 records, but it is inserting only one record.

Sample 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>

This is my insert statement:

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)

Expected output is:

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

Actual output is:

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

You have invalid xml structure. I think, for each subtask, you must taskID tag. — Paresh J
– Paresh J, Commented Aug 30, 2016 at 15:56

marc_s · Accepted Answer · 2016-08-30 16:18:56Z

You need to use a second call to .nodes() to handle the multiple <SubTask> entries you might have.

Try this:

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)

This should return the output you're looking for. You need to get the /Project/Task/SubTask nodes as a list of XML sub-fragments, too - and you need to grab the SubTaskId and Hours values from those child nodes to get all the information

@user3791199: If you feel this answer helped you solve your problem, then please accept this answer. This will show your appreciation for the people who spent their own time to help you.

Collectives™ on Stack Overflow

Insert data into table in SQL Server from XML

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related