mssql中用xml的方法拆分以不定空格为分割符号的字符串

---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                                   &nb