Split strings and convert single column to multiple rows by using XQuery

Copied from http://www.mssqltips.com/sqlservertip/1771/splitting-delimited-strings-using-xml-in-sql-server/

 

 

DECLARE  @t TABLE (
    ID INT IDENTITY
    ,data VARCHAR(50)
    )

INSERT INTO @t (data)
SELECT 'AA,AB,AC,AD'

INSERT INTO @t (data)
SELECT 'BA,BB,BC'

SELECT *
FROM @t

SELECT ID
    ,data
    ,d.r.value('.', 'nvarchar(20)') dd
FROM (
    SELECT ID
        ,data
        ,CAST('<data>' + REPLACE(data, ',', '</data><data>') + '</data>' AS XML) xmldata
    FROM @t
    ) a
CROSS APPLY a.xmldata.nodes('data') AS d(r)