SQL Server 2005: Split string XML Style

--Here's a Split function using XML datatype.

--It's preety neat and simple compared to all others that i've seen.

--Forget While Loops and recursive CTE's.

--Enter XML:

IF OBJECT_ID('dbo.Split') IS NOT NULL

    DROP FUNCTION dbo.Split

GO

CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))

RETURNS @t TABLE (data NVARCHAR(max))

AS

BEGIN

   

    DECLARE @textXML XML;

    SELECT    @textXML = CAST('' + REPLACE(@data, @delimiter, '') + '' AS XML);

    INSERT INTO @t(data)

    SELECT  T.split.value('.', 'nvarchar(max)') AS data

    FROM    @textXML.nodes('/d') T(split)

   

    RETURN

END

GO

DECLARE @text NVARCHAR(max)

SELECT @text = REPLICATE('ab,', 300) + 'ab'

SELECT * FROM dbo.Split(@text, ',')