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,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) */
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) 试试这种方法
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 */
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)
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)
*/
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)
试试这种方法
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
*/