/*
现在有一个长字符串（长度为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

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 code

declare @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) x
from (select @x x) a
inner join master.dbo.spt_values b
on b.[type]='P' and substring('<?XML'+a.x,b.number,5)='<?XML'

/*
x
------------------
1
2
3
4
5

(5 row(s) affected)
*/

------解决方案--------------------
SQL code
DECLARE @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 #t
FROM @xml.nodes('x') T(c)

------解决方案--------------------
SQL code

create table #t(
value int
)
go
declare @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
/*
value
1
2
3
4
5
6
7
8
9
*/