TSQL Snippet: Split string in records

Case
I have a string in TSQL and I want to split it into separate values / records. How do I do that?

Solution
There are a lot of split examples available on the web, but I really like the XQuery solution for this. First you add a begin XML tag in front of your list and an closing XML tag at the end. Then you replace all separators by a closing and a begin tag. After that you have an XML string and you can use Xquery to split it. Below a little snippet as part of a stored procedure, but you could also create a function for it or just use the three lines in your own code:

-- Snippet
CREATE PROCEDURE [dbo].[SplitList] (
      @List VARCHAR(255)
    , @Separator VARCHAR(1)
)
as
BEGIN
    DECLARE @Split XML;
    SET @Split = CAST('<t>' + REPLACE(@List, @Separator, '</t><t>') + '</t>' as XML) 
    SELECT Col.value('.', 'VARCHAR(255)') as ListValue FROM @Split.nodes('t') as xmlData(Col)  order by 1
END

Note: your string / list can't contain forbidden XML characters like <, > and &. You could use additional REPLACE functions to prevent errors: REPLACE(@List,"<", "&lt;")

split snippet