---xml拆分以不定空格为分割符号的字符串
--测试数据
if object_id('[tb]') is not null drop table [tb]
create table [tb]([a] varchar(200))
go
insert [tb]
select 'aaaa bbbb cccc dddd'
insert [tb]
select 'eeeeee ffff hhhh yyyy '
--开始查询
select
rtrim(A.A) as a,B.A2
from (
select a,convert(xml,'<root><row>'+replace(replace(rtrim(ltrim(a)),' ','</row><row>'),'<row></row>','')+'</row></root>') AS A1
from [tb]
) a outer apply (select a2=T.C.value('.','nvarchar(100)') from a.a1.nodes('/root/row')T(C)) B
/*
a A2
-------------------------------------------------------------------------- --------------------
aaaa bbbb cccc dddd aaaa
aaaa bbbb cccc dddd bbbb
aaaa bbbb cccc dddd cccc
aaaa bbbb cccc dddd dddd
eeeeee ffff hhhh yyyy eeeeee
eeeeee ffff hhhh yyyy ffff
eeeeee ffff hhhh yyyy hhhh
eeeeee ffff hhhh yyyy yyyy
(8 行受影响)
*/