SQL Server:How to split comma delimited string into rows?

Most of the time we required to split the comma separated string into number of row to insert or some time use it inside the  “in clause”  for DML operation sql query, here is a very good example using the power of XML in SQL query you can do it very easy way. This the best split function in sql 2005/2008 to split a comma separated value or string into temp table  using XML.

Step 1

First of all cast the @commasepvalue  into an XML data type by replacing the “,” comma delimiter with starting and ending tags ''.

declare @commasepvalue varchar(50)='rashmi,ashish,punit,vishal'

SELECT cast('<x>'+replace(@commasepvalue,',','</x><x>')+'</x>' as xml) 

as thexml

 Use 'CROSS APPLY' for splitting the data. The APPLY clause lets you join a table to a table-valued-function. The APPLY clause acts like a JOIN without the ON clause and comes in two flavors: CROSS and OUTER. 
* The OUTER APPLY clause returns all the rows on the left side (@commasepvalue) 

whether they return any rows in the table-valued-function or not. The columns that the
table-valued-function returns are null if no rows are returned.
* The CROSS APPLY only returns rows from the left side (@commasepvalue) 
if the table-valued-function returns rows.

declare @commasepvalue varchar(50)='rashmi,ashish,punit,vishal'

select q2.value from

(SELECT cast('<x>'+replace(@commasepvalue,',','</x><x>')+'</x>' as xml) as thexml)q1 CROSS APPLY

(SELECT x.value('.','varchar(100)') as value FROM thexml.nodes('x') as f(x))q2

Oupput of the Above query is