Using XML to parse strings in SQL

I like finding clever ways to solve problems -- like in a previous post talking about how to parse filenames with T-SQL.

I read an article today by Divya Agrawal that used XML to parse strings in SQL and wanted to try it out in some code we use quite frequently: given a string (a list of values) and a delimiter, return the values in a table.

We have used custom functions like this for some time. Because there is no concept of a "list" in TSQL, we took a string and essentially parsed it to split the values into something we could work with. So the query:

select * from dbo.fn_varCharListToTable('my,name,is,bill',',')

would return

autoid listitem
1 my
2 name
3 is
4 bill

and you could then join that function's result however you needed.

The custom function you need to do this is very small when you use XML to parse the string:

ALTER Function [dbo].[fn_varCharListToTable] (@varcharlist varchar(max), @delimiter varchar(1)) 
returns @myTable table (autoid int IDENTITY (1,1), listitem varchar(max))
AS
begin

 declare @strAsXML as xml
 set @strAsXML = cast(('<x>'+replace(@varcharlist,@delimiter,'</x><x>')+'</x>') as xml)
 
 insert into @myTable(listitem)
 select N.value('.','varchar(max)') as value
 from @strAsXML.nodes('x') as T(N)

 return
end