sql 读取xml文件 解析xml文件方法

 sql 读取xml文件 解析xml文件方法

declare @xml xml =
'<OdfBody DocumentType="DT_General" Time="1587952200" Date="2020-04-27" Source="CENTER" Version="62" LogicalDate="2020-04-27" CompetitionCode="0001" DocumentCode="PFT0001----------------">
    <Competition SportName="010001体能达标测试" EndDate="2020-04-25" BeginDate="2020-04-25" SportCode="0001" BatchCode="01">
        <Batch Code="01" Name="自测TEXT">
            <CompetitionRule Score="1.0" Level="达标"/>
            <Venue Venue="V001" VenueName="xxx体育馆" LocationName="" Location=""/>
            <Discipline Order="1" Code="003" DisciplineName="板块体能测试" GroupCode="0001" GroupName="组一">
                <DisplineRule Score="80.0" Level="达标"/>
                <Event Order="1" EventName="80%以上最大心率保持时间" Unit="s" EventCode="202" ExtInfo=""/>
                <Event Order="2" EventName="攀爬机距离" Unit="m" EventCode="201" ExtInfo=""/>
            </Discipline>
            <Discipline Order="2" Code="002" DisciplineName="躯干稳定性得分和力量均衡性测试" GroupCode="0001" GroupName="组一">
                <DisplineRule Score="70.0" Level="达标"/>
                <Event Order="1" EventName="上肢峰值功率" Unit="w" EventCode="111" ExtInfo=""/>
                <Event Order="2" EventName="下肢比值 %" Unit="%" EventCode="110" ExtInfo=""/>
                <Event Order="3" EventName="下肢峰值功率" Unit="w" EventCode="112" ExtInfo=""/> 
            </Discipline>
            <Discipline Order="3" Code="001" DisciplineName="基础体能测试" GroupCode="0001" GroupName="组一">
                <DisplineRule Score="60.0" Level="达标"/>
                <Event Order="1" EventName="2000m测功仪" Unit="s" EventCode="016" ExtInfo=""/>
                <Event Order="2" EventName="引体向上" Unit="s" EventCode="011" ExtInfo=""/>
                <Event Order="3" EventName="垂直纵跳" Unit="cm" EventCode="006" ExtInfo=""/> 
            </Discipline>
        </Batch>
    </Competition>
</OdfBody>'
--declare @xml xml
--set @xml = @xmlStr
----------------------------------
create table #Disc(
	[index] int identity(1,1),
	Code nvarchar(50),
	DisciplineName nvarchar(50),
	GroupCode nvarchar(50),
	[Order]  nvarchar(50),
	F_IsActive int
)

create table #DiscRule( 
	[index] int identity(1,1),
	Code nvarchar(50),
	DisciplineName nvarchar(50),
	GroupCode nvarchar(50),
	[Order]  nvarchar(50),
	F_IsActive int,
	Score NVARCHAR(50),
	Levelss nvarchar(50)
)

create table #T1( 
	[index] int identity(1,1),
	Score NVARCHAR(50),
	Levelss nvarchar(50),
	 DisciplineName nvarchar(50), 
	 sportid nvarchar(50),
	  Code nvarchar(50), 
	  IsActive nvarchar(50) , 
	  [Order]  nvarchar(50)
)
	
CREATE TABLE #DANDE(
	DCode nvarchar(50),
	GroupCode nvarchar(50),  
	ECode nvarchar(50) ,
	Evename nvarchar(50),
	Unit nvarchar(50),
	ExtInfo nvarchar(50)
)
CREATE TABLE #Competition(
	SportName NVARCHAR(50),
	EndDate DATETIME,
	BeginDate DATETIME,
	SportCode NVARCHAR(50),
	BatchCode NVARCHAR(50)
)

--------------------------------------- 
 --Competition OK
INSERT INTO #Competition(SportName,BeginDate,EndDate,SportCode,BatchCode)
SELECT 
	C.value('@SportName[1]','NVARCHAR(50)') AS SportName,
	C.value('@EndDate[1]','DATETIME') AS EndDate,
	C.value('@BeginDate[1]','DATETIME') AS BeginDate,
	C.value('@SportCode[1]','NVARCHAR(50)') AS SportCode,
	C.value('@BatchCode[1]','NVARCHAR(50)') AS BatchCode
	FROM @xml.nodes('/OdfBody/Competition') T(C)  
-- select * from #Competition
 declare @SportCode nvarchar(50) ='0001'
 declare @sportcount int

  select @sportcount=count(*) from  [dbo].[TS_Sport] sp where sp.F_RSCcode= @SportCode 
  if	(@sportcount>0)
		begin
			delete  from   [dbo].[TS_Sport]  where F_RSCcode= @SportCode;
			insert into [dbo].[TS_Sport] select comp.SportName,comp.SportCode,'11',comp.BeginDate,comp.EndDate,'' from #Competition as comp;
		end
  else	
		begin
			insert into [dbo].[TS_Sport] select comp.SportName,comp.SportCode,'11',comp.BeginDate,comp.EndDate,'' from #Competition as comp;
		end
		
 --Competition OK
 ---------------------------------------
 select * from #Competition

 --------------------------------------- 
 CREATE TABLE #Venue(
	Venue NVARCHAR(50), 
	VenueName NVARCHAR(50) 
)
 --Venue OK
INSERT INTO #Venue(Venue,VenueName)
SELECT 
	C.value('@Venue[1]','NVARCHAR(50)') AS Venue, 
	C.value('@VenueName[1]','NVARCHAR(50)') AS VenueName 
	FROM @xml.nodes('/OdfBody/Competition/Batch/Venue') T(C)  
-- select * from #Competition
 declare @Venue nvarchar(50) 
 declare @VenueName nvarchar(50)
 declare @venueCount nvarchar(50)

  select @venueCount=count(*) from [dbo].[TC_Venue] v where v.F_VenueName= @VenueName and v.F_RSCcode=@Venue
  if	(@venueCount<1)
		begin 
			insert into [dbo].[TC_Venue] select v.VenueName,v.Venue  from #Venue as v;
		end 
		select * from #Venue
 --Venue OK
 ---------------------------------------



 --------------------------------------- 
 CREATE TABLE #OdfBody(
	oDocumentType NVARCHAR(50), 
	oTime NVARCHAR(50), 
	oDate datetime, 
	oSource NVARCHAR(50), 
	oVersion NVARCHAR(50),
	oLogicalDate datetime,
	oCompetitionCode NVARCHAR(50),
	oDocumentCode NVARCHAR(50) 
)
 
  
 --Batch    OK
 CREATE TABLE #Batch(
	BatchCode NVARCHAR(50),
	BatchName NVARCHAR(50) 
)
INSERT INTO #Batch(BatchCode,BatchName)
SELECT 
	C.value('@Code[1]','NVARCHAR(50)') AS BatchCode,   -- code==order 
	C.value('@Name[1]','NVARCHAR(50)') AS BatchName 
	FROM @xml.nodes('/OdfBody/Competition/Batch/Venue') T(C) 

  
  declare @BatchName nvarchar(50)  
  declare @BatchOrder nvarchar(50)  
  declare @batchCount int
  select @BatchName=le.F_LevelName,@BatchOrder=le.F_Order from [dbo].[TC_level] le 
  
 select @venueCount=count(*) from [dbo].[TC_Venue] v where v.F_VenueName= @VenueName and v.F_RSCcode=@Venue
  if	(@venueCount<1)
		begin 
			insert into [dbo].[TC_level]    select  b.BatchName,b.BatchCode   from #Batch   b
		end 
		select * from #Batch
 --Batch   OK    
---------------------------------TC_Event------------------TC_Discipline-------------------------------------
declare @i int = 1
insert into #Disc(Code,DisciplineName,GroupCode,[Order],F_IsActive)
 SELECT
    C.value('@Code[1]','NVARCHAR(50)') AS Code,
    C.value('@DisciplineName[1]','NVARCHAR(50)') AS DisciplineName,
    C.value('@GroupCode[1]','NVARCHAR(50)') AS GroupCode,
	C.value('@Order[1]','NVARCHAR(50)') AS [Order],
	1  
  FROM @xml.nodes('/OdfBody/Competition/Batch/Discipline') T(C)


insert into #DiscRule(Score,Levelss)
 SELECT
    C.value('@Score[1]','NVARCHAR(50)') AS Score,
    C.value('@Level[1]','NVARCHAR(50)') AS  [Level]
  FROM @xml.nodes('/OdfBody/Competition/Batch/Discipline/DisplineRule') T(C)

    

 declare @DC nvarchar(50),@GC nvarchar(50) 
 declare @dCount int = 0
 select @dCount= count(*) from #Disc

  -------------------------------------- 
  --  ru.Levelss,（达标） select * from [TC_Discipline]
  --直接插入
   insert into [dbo].[TC_Discipline]  select d.DisciplineName ,1 传入参数sport,d.Code,1 初始有效 ,ru.Score,d.[Order] from #DiscRule ru left join #Disc d on d.[index]=ru.[index]; 
 ---------------------------------------
	
 while @i<=@dCount
 begin
	select top 1 @DC = Code,@GC = GroupCode from #Disc where [index]= @i

	insert into #DANDE
	SELECT
	@DC,@GC, 
    C.value('@EventCode[1]','NVARCHAR(50)') AS EventCode,
    C.value('@EventName[1]','NVARCHAR(50)') AS EventName,
    C.value('@Unit[1]','NVARCHAR(50)') AS Unit,
    C.value('@ExtInfo[1]','NVARCHAR(50)') AS ExtInfo 
	FROM @xml.nodes('/OdfBody/Competition/Batch/Discipline[@Code=sql:variable("@DC")][@GroupCode=sql:variable("@GC")]/Event') T(C) 
	set @i=@i+1
 end 
---------------------------------TC_Event------------------TC_Discipline-------------------------------------
 
  
						--插入 TC_Event--
   --直接插入   insert into [dbo].[TC_Event] select g.Evename,g.DCode,g.ECode,1,1,1,1 from #DANDE g
						--插入 TC_Event--
  
  ------------------
  drop table #T1
  drop table #Disc
  drop table #DANDE  
  drop table #DiscRule
  drop table #Competition
  drop table #Batch
  drop table #OdfBody
  drop table #Venue