34,591
社区成员
发帖
与我相关
我的任务
分享
Declare @x varchar(2000)
declare @y xml
set @x='date1="2010-1-15 14:07:45" pe="pp" id="9411" name="001" date1="2010-1-15 14:07:06" pe="pp" id="09411" name="002" date1="2010-1-15 14:07:09" pe="pp" id="109411" name="003" '
set @x=replace(@x,'date1','/><x date1')
set @x=right(@x,len(@x)-1)+'/>'
set @y=cast(@x as xml)
select T.col.value('@date1','datetime') date1,
T.col.value('@pe','varchar(20)') pe,
T.col.value('@id','varchar(20)') id,
T.col.value('@name','varchar(20)') name
from @y.nodes('//x') as T(col)
--result
/*date1 pe id name
----------------------- -------------------- -------------------- --------------------
2010-01-15 14:07:45.000 pp 9411 001
2010-01-15 14:07:06.000 pp 09411 002
2010-01-15 14:07:09.000 pp 109411 003
(3 行受影响)*/
--先导入DB再处理,直接导入一个栏位中即可
create table T(col nvarchar(300))
insert into T values('date1="2010-1-15 14:07:45" pe="pp" id="9411" name="001"')
insert into T values('date1="2010-1-15 14:07:06" pe="pp" id="09411" name="002" ')
insert into T values('date1="2010-1-15 14:07:09" pe="pp" id="109411" name="003" ')
GO
select parsename(col,1) as [name], parsename(col,2) as [id], parsename(col,3) as [pe], parsename(col,4) as [date]
from
(
select replace(replace(replace( replace(replace(col,'date1="',''),'" pe="','.'),'" id="','.'),'" name="','.') ,'"','') as col
from T
) A
/*
name id pe date
-------------------------------------------------
001 9411 pp 2010-1-15 14:07:45
002 09411 pp 2010-1-15 14:07:06
003 109411 pp 2010-1-15 14:07:09
*/
Go
Drop table T