SQL Server FUN - Split received string using XML

Several time, we have a need to split the string based on a delimiter, for instance to handle multiple input filter parameters etc.

Most of the time we end up using a user defined function that accepts a string and a delimiter and returns a table of split values. Function may be in-efficient at times.

Here is an XML way to split the input string and get a result within a table

declare @xml as xml

,@str as varchar(100)

,@delimiter as varchar(10)

set @str=’A,B,C,D,E’

set @delimiter =’,’

set @xml = cast((‘<X>’ + replace(@str, @delimiter, ‘</X><X>’) + ‘</X>’) as xml)

select N.value(‘.’, ‘varchar(10)’) as value from @xml.nodes(‘X’) as T(N)

SQL Server FUN - Split received string using XML