SQL Query to Convert XML to a Table

Sometimes a developer might get a certain task to convert XML data to a table in SQL Server. In such scenarios, this post would be helpful to achieve the goal.

We have this sample XML File which we will be converting to a table:

<?xml version="1.0" encoding="utf-8" ?>
<STUDENTS>
  <STUDENT>
    <StudentID>1</StudentID>
    <Name>John Smith</Name>
    <Marks>200</Marks>
  </STUDENT>
  <STUDENT>
    <StudentID>2</StudentID>
    <Name>Mark Johnson</Name>
    <Marks>300</Marks>
  </STUDENT>
<STUDENT>
    <StudentID>3</StudentID>
    <Name>Nitin Tyagi</Name>
    <Marks>400</Marks>
  </STUDENT>
</STUDENTS>

Let us write SQL Query to convert the preceding XML to a table:

DECLARE @XMLData XML
SET @XMLData ='
<STUDENTS>
  <STUDENT>
    <StudentID>1</StudentID>
    <Name>John Smith</Name>
    <Marks>200</Marks>
  </STUDENT>
  <STUDENT>
    <StudentID>2</StudentID>
    <Name>Mark Johnson</Name>
    <Marks>300</Marks>
  </STUDENT>
<STUDENT>
    <StudentID>3</StudentID>
    <Name>Nitin Tyagi</Name>
    <Marks>400</Marks>
  </STUDENT>
</STUDENTS>'


SELECT StudentID = Node.Data.value('(StudentID)[1]', 'INT')
        , [Name] = Node.Data.value('(Name)[1]', 'VARCHAR(MAX)')
        , [Marks] = Node.Data.value('(Marks)[1]', 'INT')

FROM    @XMLData.nodes('/STUDENTS/STUDENT') Node(Data)

Execute the preceding query and check the output: