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.