sqlserver 表中的字段拆分（一行变多行XML）

create table OneTomore
(
	id int identity(1,1),
	Name varchar(255)
)
;
insert into OneTomore values('1*2*3*4*5')
insert into OneTomore values('1*2*3*4*5')
insert into OneTomore values('1*2*3*4*5*****')

;
;with mycte as
(
	 SELECT 
        ID, 
        CAST('<i>' + REPLACE(Name, '*', '</i><i>') + '</i>' AS XML) AS KID 
    FROM OneTomore 
) 

select ID,x.i.value('.', 'VARCHAR(10)') AS KID 
from mycte
CROSS APPLY KID.nodes('i') x(i) 
where  len(x.i.value('.', 'VARCHAR(10)'))<>''