XML--使用XML来将字符串分隔成行数据

DECLARE @xml XML
SET @xml=CAST(REPLACE('<ROOT><X>'+'AA,AB,AC,AD'+'</X></ROOT>',',','</X><X>') AS XML)
 
SELECT T.C.value('.','varchar(20)') AS Result 
FROM @xml.nodes('/ROOT/X') T(C)

 

取前几条记录，如'10,102,10254,103265,541,2154,41,156'中第四个','前的字符串

DECLARE @sourceStr NVARCHAR(MAX)
DECLARE @splitStr NVARCHAR(10)
SET @sourceStr='10,102,10254,103265,541,2154,41,156'
SET @splitStr=','
DECLARE @xml XML
SET @xml=CAST(REPLACE('<ROOT><X>'+@sourceStr+'</X></ROOT>',@splitStr,'</X><X>') AS XML)

SELECT TOP(3) Result+',' AS [text()]
FROM 
(
 SELECT T.C.value('.','varchar(20)') AS Result 
 FROM @xml.nodes('/ROOT/X') T(C)
) AS T1 
FOR XML PATH('')