SQL 2005中利用XML對字符串拆分的方法

1、常規方法（可運用于SQL SERVER 2000中）

DECLARE @str varchar(1000)
DECLARE @idoc int;
DECLARE @doc xml;

set @str='aaa,bbb,ccc'
set @doc=cast('<Root>
<item><ID>'+replace(@str,',','
</ID></item><item><ID>')+'</ID></item></Root>' as xml)
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc

SELECT * FROM OPENXML (@Idoc, '/Root/item',2)
WITH (
[ID] varchar(10)
)
/**//*
ID
-----------
aaa

bbb

ccc

*/

2、運用SQL SERVER 2005 中的新類型xml

declare @str varchar(1000)
declare @x xml
set @str='aaa,bbb,ccc'
SELECT @x = CONVERT(xml,
'<v>' + REPLACE(@str, ',', '</v><v>') + '</v>')

SELECT N.v.value('.', 'varchar(100)')
FROM @x.nodes('/v') N(v)

3、運用SQL SERVER 2005 中的新類型xml及OUTER APPLY一句SQL搞定

DECLARE @str VARCHAR(1000)
DECLARE @x xml
SET @str='aaa,bbb,ccc'
SELECT id
FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@str , ',' , '</v><v>')+ '</v>')
) A
OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N (v)
) B

其實第二種與第三種是一樣的，只不過第二種用了一個中間變量來處理