xml的应用

declare @s varchar(1000)

set @s='a,b,c,d,e,f,g,asdf,erqer,dfasd,vvvv,eeee,LLL'

--找出4,5个之间，其实也就是第5个。替换为 nnn

DECLARE @p INT,@v VARCHAR(10)

SET @p=5

SET @v='nnn'

DECLARE @x XML

SET @x = CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML)

SET @x.modify('replace value of (//r[position()=5]/text())[1] with sql:variable("@v")')

SELECT @s=CAST(@x.query('for $v in //r return concat($v,",")') AS VARCHAR(1000))

SELECT @s