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.