从XML查询属性

以下为一位同事服务器上保存的某LOG,格式为TXT。

需要解析成如下格式的数据

/*

col_time                                      catName           NodeNmae      value

-------------------------------------------------    ---------     ------------- ---------

 00000001 20100708-090744 Stat     00400000 0B8       lv3           SpeedIndi2    1

 00000001 20100708-090744 Stat     00400000 0B8       lv3           SpeedIndi3    1

........................

 

 

*/

TXT 文件如下格式

/*

*-*-*-*-*-* <begin type='ordinal localTime srcKey type msg' tz='8' time='20100708-070740' uuid='2cae351f-ab2d-4b4c-9f39-815aeccc6fbb'/>

 00000001 20100708-090744 Stat     00400000 0B8 <LgGameCount catName='lv3' SpeedIndi2='1' SpeedIndi3='1' SpeedTeam2='2' ItemTeam8='1' BattleTeam4='2' DeadRacingTeam2='7' DeadRacingTeam4='2' DeadRacingTeam7='2' DeadRacingTeam8='6' />

 00000002 20100708-090744 Stat     00400000 0BD <LgGameCount catName='lv3' SpeedIndil3='4' SpeedIndil2='1' SpeedTeaml3='3' SpeedTeaml1='1' ItemTeaml3='8' BattleTeaml3='8' DeadRacingTeaml3='75' DeadRacingTeaml2='8' DeadRacingTeaml1='1' />

........................

 0000001D 20100708-230742 Stat     00400000 303 <LgGameCount catName='lv3' SpeedIndi1='13' SpeedIndi2='575' SpeedIndi3='142' SpeedIndi4='64' SpeedIndi5='44' SpeedIndi6='30' SpeedIndi7='49' SpeedIndi8='96' ItemIndi1='6' ItemIndi2='194' ItemIndi3='55' ItemIndi4='64' ItemIndi5='14' ItemIndi6='30' ItemIndi7='73' ItemIndi8='278' SpeedTeam1='30' SpeedTeam2='710' SpeedTeam3='8' SpeedTeam4='159' SpeedTeam5='14' SpeedTeam6='79' SpeedTeam7='91' SpeedTeam8='695' ItemTeam1='5' ItemTeam2='91' ItemTeam3='4' ItemTeam4='46' ItemTeam5='2' ItemTeam6='57' ItemTeam7='85' ItemTeam8='899' BattleTeam1='17' BattleTeam2='337' BattleTeam3='361' BattleTeam4='1216' DeadRacingTeam1='14' DeadRacingTeam2='1055' DeadRacingTeam3='11' DeadRacingTeam4='198' DeadRacingTeam5='9' DeadRacingTeam6='95' DeadRacingTeam7='79' DeadRacingTeam8='999' />

 0000001E 20100708-230742 Stat     00400000 189 <LgGameCount catName='lv3' SpeedIndil3='2263' SpeedIndil2='737' SpeedIndil1='356' ItemIndil3='3522' ItemIndil2='174' ItemIndil1='104' SpeedTeamrookie='6' SpeedTeaml3='6764' SpeedTeaml2='1001' SpeedTeaml1='1080' ItemTeaml3='7442' ItemTeaml2='713' ItemTeaml1='367' BattleTeaml3='6201' BattleTeaml2='346' BattleTeaml1='91' DeadRacingTeaml3='10456' DeadRacingTeaml2='913' DeadRacingTeaml1='740' />

 0000001F 20100709-000036 Stat     00400000 2D9 <LgGameCount catName='lv3' SpeedIndi1='4' SpeedIndi2='151' SpeedIndi3='98' SpeedIndi4='46' SpeedIndi5='24' SpeedIndi6='9' SpeedIndi7='13' SpeedIndi8='16' ItemIndi1='4' ItemIndi2='105' ItemIndi3='36' ItemIndi4='15' ItemIndi5='6' ItemIndi6='12' ItemIndi7='35' ItemIndi8='58' SpeedTeam1='9' SpeedTeam2='297' SpeedTeam3='8' SpeedTeam4='108' SpeedTeam5='3' SpeedTeam6='50' SpeedTeam7='38' SpeedTeam8='216' ItemTeam1='4' ItemTeam2='25' ItemTeam4='27' ItemTeam6='15' ItemTeam7='23' ItemTeam8='375' BattleTeam1='6' BattleTeam2='85' BattleTeam3='129' BattleTeam4='380' DeadRacingTeam1='3' DeadRacingTeam2='435' DeadRacingTeam3='4' DeadRacingTeam4='77' DeadRacingTeam5='4' DeadRacingTeam6='37' DeadRacingTeam7='25' DeadRacingTeam8='274' />

*-*-*-*-*-* <end time='20100709-000036' uuid='2cae351f-ab2d-4b4c-9f39-815aeccc6fbb' continue='true'/>

*/

 

下面是处理代码

 

 

--把TXT文件导入表中

IF OBJECT_ID('[logTB]') IS NOT NULL

DROP TABLE logTB

GO

 

CREATE  TABLE logTB(detail VARCHAR(MAX))

GO

 

 

BULK INSERT logTB

FROM 'c:/crm log.txt'

 

GO

--删除文件的头尾行

DELETE FROM logTB WHERE detail like '*-*-*-*-*-*%'

go

 

--把文件解析成XML格式导入表中

IF OBJECT_ID('TEMPDB..#t1') IS NOT NULL

DROP TABLE #t1

GO

 

SELECT  CONVERT(VARCHAR(MAX),LEFT(detail,PATINDEX('%<%',detail)-1)) as t1,

CONVERT(XML,RIGHT(detail,LEN(detail)-PATINDEX('%<%',detail)+1)) as t2

into #t1

from logTB

 

 

--解析成对应的格式

 

select

t1

,t2.value('(LgGameCount/@catName)[1]','varchar(20)') as catName

,C.value('local-name(.)','varchar(200)') as NodeNmae

,C.value('.','varchar(max)')  as value

from #t1  cross apply t2.nodes('/*/@*[local-name()!="catName"]') T(C)

 

 

 

/*

t1                                               catName              NodeNmae              value

-------------------------------------------------       -----------          ---------------           --------

 00000001 20100708-090744 Stat     00400000 0B8           lv3               SpeedIndi2            1

 00000001 20100708-090744 Stat     00400000 0B8           lv3               SpeedIndi3            1

 00000001 20100708-090744 Stat     00400000 0B8           lv3               SpeedTeam2            2

 ...............                                                                      

 0000001F 20100709-000036 Stat     00400000 2D9           lv3               DeadRacingTeam7          25

 0000001F 20100709-000036 Stat     00400000 2D9           lv3               DeadRacingTeam8          274

 

(938 行受影响)

*/