Parsing XML Data from SQL Database Column

There are scenarios when we will have data dumped in to Database Column in XML format.

Now when we are reading those data it does not make sense to present the data in the XML format.
Here are the example where we have XML data in a below format.

declare @Employee as nvarchar(max)
declare @EmployeeXML as XML
declare @cnt int
   SET @EmployeeXML='<Employees>
    <Employee>
    <EmployeeID>Anoj Singh</EmployeeID>
    <EmployeeName>150025</EmployeeName>
    <Department>Delivery</Department>
</Employee>
<Employee>
    <EmployeeID>Ashutosh Mund</EmployeeID>
    <EmployeeName>150016</EmployeeName>
    <Department>Sales</Department>
</Employee>
<Employee>
    <EmployeeID>Nihil Jain</EmployeeID>
    <EmployeeName>150017</EmployeeName>
    <Department>Finance</Department>
</Employee>
<Employee>
    <EmployeeID>Sravan Kumar</EmployeeID>
    <EmployeeName>150018</EmployeeName>
    <Department>Admin</Department>
</Employee>
</Employees>'

SELECT b.value('(EmployeeID)[1]', 'NVARCHAR(200)')+','+
       b.value('(EmployeeName)[1]', 'NVARCHAR(200)')+','+
       b.value('(Department)[1]', 'NVARCHAR(200)') as EmployeeData
       FROM  @EmployeeXML.nodes('Employees/Employee') a(b)
       print @Employee

This will display the data as shown below.

There are other way and format also which I will share in subsequent posts.