Wednesday, January 21, 2015

SQL Server 2008/2012 - Loop through/split a delimited string

On internet, you will find many ways to loop trough the comma separated list in SQL. Following is a little bit different thing. Comma Separated list is parsed using XML datatype is saved in a temporary table.

Declare @xml AS XML  
Declare @id_list  VarChar(Max)
SET @id_list  = '2,5,6,2,6,73,26,267'

  IdSequence  INT  
SET @xml = CAST(('' + replace(@id_list , ',' ,'')+'') AS XML)  
INSERT INTO #TmpIdlist (IdSequence)  
SELECT  N.value('.', 'INT') AS value  
FROM @xml.nodes('A') AS T(N)