Post
Ask Question

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.

XML Code

  1. declare @Employee as nvarchar(max)  
  2. declare @EmployeeXML as XML  
  3. declare @cnt int  
  4.    SET @EmployeeXML='<Employees>     
  5.     <Employee>  
  6.     <EmployeeID>Anoj Singh</EmployeeID>  
  7.     <EmployeeName>150025</EmployeeName>  
  8.     <Department>Delivery</Department>  
  9. </Employee>  
  10. <Employee>  
  11.     <EmployeeID>Ashutosh Mund</EmployeeID>  
  12.     <EmployeeName>150016</EmployeeName>  
  13.     <Department>Sales</Department>  
  14. </Employee>  
  15. <Employee>  
  16.     <EmployeeID>Nihil Jain</EmployeeID>  
  17.     <EmployeeName>150017</EmployeeName>  
  18.     <Department>Finance</Department>  
  19. </Employee>  
  20. <Employee>  
  21.     <EmployeeID>Sravan Kumar</EmployeeID>  
  22.     <EmployeeName>150018</EmployeeName>  
  23.     <Department>Admin</Department>  
  24. </Employee>  
  25. </Employees>'   
  26.   
  27. SELECT b.value('(EmployeeID)[1]', 'NVARCHAR(200)')+','+  
  28.        b.value('(EmployeeName)[1]', 'NVARCHAR(200)')+','+  
  29.        b.value('(Department)[1]', 'NVARCHAR(200)') as EmployeeData  
  30.        FROM  @EmployeeXML.nodes('Employees/Employee') a(b)  
  31.        print @Employee  

This will display the data as shown below.

Employee data

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