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 mycteCROSS APPLY KID.nodes('i') x(i) where  len(x.i.value('.', 'VARCHAR(10)'))<>''