一个字符串拆分到多行？(求改善！)_Sql Server

一个字符串拆分到多行？(求改进！)

SQL code

/*现在有一个长字符串（长度为444,264,676）（分割标识符为：<?XML ），我想将它分割到多行(最终结果大概5000行左右).我用了下面这个方法，虽然结果正确，但是很费时（约2个小时）。请问有什么其他效率高的方法吗？谢谢各位大侠！*/        declare @doc varchar(max),@Stop bit,@xml varchar(max),@pos int,@PrePos int,@i int    select @doc=BulkColumn from Table_2011 --原有字符串所在表    select @Stop =1, @i=1, @PrePos=0,@Pos=0    while @Stop=1    begin        set @PrePos=@PrePos+@Pos        set @pos=patindex('%<?xml%',right(@doc,len(@doc)-1-@PrePos))          if @pos=0        begin            select @xml =substring(@doc,@prepos,LEN(@DOC)-@prePOS)            set @Stop =0        end        else        begin            set @xml =substring(@doc,@prepos,@pos)        end                                       insert into Final_Table values (@i,@xml)--写入到新表        set @i=@i+1                         end

------解决方案--------------------
try

SQL code

DECLARE @one Table(        CompanyID INT,        CompanyCodes VARCHAR(100) )  insert into @one select 1,'1<?xml2' union all select 2,'1<?xml2<?xml3' union all select 3,'1<?xml2<?xml3<?xml4'union all select 4,'1<?xml2<?xml3<?xml4<?xml5';WITH cte AS (     SELECT         CompanyID,         CAST('<i>' + REPLACE(CompanyCodes, '<?xml', '</i><i>') + '</i>' AS XML) AS CompanyCodes     FROM @one ) SELECT     CompanyID,     x.i.value('.', 'VARCHAR(10)') AS CompanyCode FROM cte CROSS APPLY CompanyCodes.nodes('i') x(i)
------解决方案--------------------
try this,
SQL codedeclare @x varchar(500)select @x='1<?XML2<?XML3<?XML4<?XML5'select substring(a.x,b.number,charindex('<?XML',a.x+'<?XML',b.number)-b.number) xfrom (select @x x) ainner join master.dbo.spt_values bon b.[type]='P' and substring('<?XML'+a.x,b.number,5)='<?XML'/*x------------------12345(5 row(s) affected)*/
------解决方案--------------------
SQL codeDECLARE @s VARCHAR(1000), @xml xml;SET @s = '1<?xml2<?xml3<?xml4<?xml5' ;SET @xml = CAST('<x>' + REPLACE(@s, '<?xml', '</x><x>') + '</x>' AS XML)SELECT T.c.value('(.)[1]','varchar(10)')--INTO #tFROM @xml.nodes('x') T(c)
------解决方案--------------------
SQL codecreate table #t(value int)godeclare @str varchar(max)set @str='1<?XML2<?XML3<?XML4<?XML5<?XML6<?XML7<?XML8<?XML9'select @str='insert #t select '+REPLACE(@str,'<?XML',' union all '+CHAR(10)+' select ')exec(@str)select * from #t/*value123456789*/