Split single String column into Multiple Columns in sql server

We have a log table in that table we are logging the information with the Pipe (|) separated value in that column. We want to split this single column into multiple columns.

See the example.

We have an employee table in that we are storing the address with Pipe separated.  

Now we need to split the Emp_add into multiple columns.

Using xml_data.value we can achieve the same.

See the below sql script

; with cte_emp as   (   SELECT   [id]       ,[name]       ,[Mob]                  ,cast('<N>'+replace([Emp_Add],'|','</N><N>')+'</N>' as XML) as xml_data   FROM [dbo].[emp]   )   select      [id] ,[name],[Mob]   ,replace(xml_data.value('/N[1]','varchar(50)'),'Vill : ','') as vill   ,replace(xml_data.value('/N[2]','varchar(50)'),'Post : ','') as Post   ,replace(xml_data.value('/N[3]','varchar(50)'),'Thana : ','') as Thana   ,replace(xml_data.value('/N[4]','varchar(50)'),'Dist: ','') as Dist   ,replace(xml_data.value('/N[5]','varchar(50)'),'Pin : ','') as Pin   ,replace(xml_data.value('/N[6]','varchar(50)'),'State : ','') as State   from cte_emp

 Running the script.  

Got the expected result.