sql server xml nodes 的使用

--select  a.RptID,T.C.value('.','nvarchar(100)')

--from 

--(select RptID,

--  txt=convert(xml,'<root><v>'+replace(txt,',','</v><v>')+'</v></root>') 

--from Test

--)a

--outer apply a.txt.nodes('/root/v') AS T(C)

declare @TempAllPro varchar(3000)

declare @TempAllProXml xml

set @TempAllPro='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46'

set @TempAllProXml=convert(xml,'<root><v>'+Replace(@TempAllPro,',','</v><v>')+'</v></root>')

declare @ProPageHead table(Id int)

insert into @ProPageHead(Id)

select C.value('.','int')

from @TempAllProXml.nodes('/root/v') T(C)