Convert XML to Table in SQL Server

In this post, I am sharing the script to read nodes of XML and display it in Tabular Form in SQL Server

XML Declaration

DECLARE @XML XML
SET @XML =
'<NewDataSet>
    <Employee>
       <EmpID>EMP001</EmpID>
       <FirstName>Sandeep</FirstName>
       <LastName>Mittal</LastName>
       <DOB>08/25/1981</DOB>
    </Employee>
    <Employee>
       <EmpID>EMP002</EmpID>
       <FirstName>Abhay</FirstName>
       <LastName>Kumar</LastName>
       <DOB>12/01/1982</DOB>
    </Employee>
</NewDataSet>'

Above declared XML is a collection of Employee details.

Now Let's write a query to fetch the nodes from the XML Variable and display the employee details in Tabular Form.

Query to get data in Table from XML Variable

SELECT EmpID = Node.Data.value('(EmpID)[1]', 'VARCHAR(MAX)')
        , [First Name] = Node.Data.value('(FirstName)[1]', 'VARCHAR(MAX)')
        , [Last Name] = Node.Data.value('(LastName)[1]', 'VARCHAR(MAX)')
        , DOB = Node.Data.value('(DOB)[1]', 'VARCHAR(MAX)')
FROM    @XML.nodes('/NewDataSet/Employee') Node(Data)

Output