This is a great method I have used a number of times. It uses a function and cross apply.

http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx