Post
Ask Question

SQL Query to Convert XML to Table

Sometimes a developer might get a certain task to convert XML data to 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 table.

  1. <?xml version="1.0" encoding="utf-8" ?>  
  2. <STUDENTS>  
  3.     <STUDENT>  
  4.         <StudentID>1</StudentID>  
  5.         <Name>John Smith</Name>  
  6.         <Marks>200</Marks>  
  7.     </STUDENT>  
  8.     <STUDENT>  
  9.         <StudentID>2</StudentID>  
  10.         <Name>Mark Johnson</Name>  
  11.         <Marks>300</Marks>  
  12.     </STUDENT>  
  13.     <STUDENT>  
  14.         <StudentID>3</StudentID>  
  15.         <Name>Nitin Tyagi</Name>  
  16.         <Marks>400</Marks>  
  17.     </STUDENT>  
  18. </STUDENTS>  
Let us write SQL Query to convert the preceding XML to table.
  1. DECLARE @XMLData XML  
  2. SET @XMLData = ' <  
  3.     STUDENTS >  
  4.     <  
  5.     STUDENT >  
  6.     <  
  7.     StudentID > 1 < /StudentID> <  
  8.     Name > John Smith < /Name> <  
  9.     Marks > 200 < /Marks> <  
  10.     /STUDENT> <  
  11.     STUDENT >  
  12.     <  
  13.     StudentID > 2 < /StudentID> <  
  14.     Name > Mark Johnson < /Name> <  
  15.     Marks > 300 < /Marks> <  
  16.     /STUDENT> <  
  17.     STUDENT >  
  18.     <  
  19.     StudentID > 3 < /StudentID> <  
  20.     Name > Nitin Tyagi < /Name> <  
  21.     Marks > 400 < /Marks> <  
  22.     /STUDENT> <  
  23.     /STUDENTS>'  
  24.   
  25.   
  26. SELECT StudentID = Node.Data.value('(StudentID)[1]', 'INT'), [Name] = Node.Data.value('(Name)[1]', 'VARCHAR(MAX)'), [Marks] = Node.Data.value('(Marks)[1]', 'INT')  
  27.   
  28. FROM @XMLData.nodes('/STUDENTS/STUDENT') Node(Data)  
Execute the preceding query and check the output.

output