SqlServer下通过XML拆分字符串的方法

时间:2022-06-01 22:17:05

代码如下:


DECLARE @idoc int; 
DECLARE @doc xml; 
set @doc=cast('<Root><item><ProjID>'+replace(@SelectedProjectArray,',','</ProjID></item><item><ProjID>')+'</ProjID></item></Root>' as xml) 
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc 

SELECT ProjID FROM OPENXML (@Idoc, '/Root/item',2) 
WITH ( 
[ProjID] varchar(10)