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)
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
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
其实第二种与第三种是一样的,只不过第二种用了一个中间变量来处理