-- =============================================
-- Author:        gguozhenqian
-- Create date: 2012-03-12
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE up_UpdateFlightStatusBat
    @Xml xml
AS
BEGIN 
     
    ;WITH FlightStatus AS (
    SELECT 
        M.a.value('@ID','int') AS id, 
        M.a.value('@KeyWord','varchar(50)') AS keyword,
        M.a.value('@DateTime','datetime') AS datetime  
       FROM  @XML.nodes('/elements/element') M(a)  
      ) 
  --列转行
  select * INTO #flightStatus from FlightStatus as t pivot(max(datetime) for keyword IN
  ([FlightOffTime],[FlightDoorCloseTime],FlightOnTime,FlightOutTime,FlightInTime) ) AS pvt
     
    --FlightOffTime  FlightDoorCloseTime FlightOnTime FlightOutTime FlightInTime
   
  --更新相对应字段的时间
   UPDATE FlightInfo WITH(ROWLOCK, UPDLOCK)
      SET FlightOffTime= b.FlightOffTime 
     FROM FlightInfo a,#flightStatus b
     WHERE a.FlightInfoID=b.id AND a.FlightOffTime IS NULL AND b.FlightOffTime IS NOT null
     
   UPDATE FlightInfo WITH(ROWLOCK, UPDLOCK)
      SET FlightDoorCloseTime= b.FlightDoorCloseTime 
     FROM FlightInfo a,#flightStatus b
     WHERE a.FlightInfoID=b.id AND a.FlightDoorCloseTime IS NULL AND b.FlightDoorCloseTime IS NOT NULL
     
   UPDATE FlightInfo WITH(ROWLOCK, UPDLOCK)
      SET FlightOnTime= b.FlightOnTime 
     FROM FlightInfo a,#flightStatus b
     WHERE a.FlightInfoID=b.id AND a.FlightOnTime IS NULL AND b.FlightOnTime IS NOT null
     
   UPDATE FlightInfo WITH(ROWLOCK, UPDLOCK)
      SET FlightOutTime= b.FlightOutTime 
     FROM FlightInfo a,#flightStatus b
     WHERE a.FlightInfoID=b.id AND a.FlightOutTime IS NULL AND b.FlightOutTime IS NOT NULL
     
   UPDATE FlightInfo WITH(ROWLOCK, UPDLOCK)
      SET FlightInTime= b.FlightInTime 
     FROM FlightInfo a,#flightStatus b
     WHERE a.FlightInfoID=b.id AND a.FlightInTime IS NULL AND b.FlightInTime IS NOT null
     
    drop TABLE #flightStatus
END





--测试  
DECLARE @xml xml
SET @xml='<elements>
      <element ID="1" KeyWord="lightOffTime" DateTime="2012-01-02" />
      <element ID="2" KeyWord="FlightOffTime" DateTime="2012-01-02" />
      <element ID="5" KeyWord=FlightDoorCloseTime DateTime="2012-01-02" />
      <element ID="4" KeyWord="FlightOffTime" DateTime="2012-01-02" />
      <element ID="1" KeyWord=FlightOnTime DateTime="2012-01-02" />
      <element ID="6" KeyWord="FlightOffTime" DateTime="2012-01-02" />
      <element ID="7" KeyWord=FlightOutTime DateTime="2012-01-02" />
      <element ID="3" KeyWord="FlightOffTime" DateTime="2012-01-02" /> 
</elements>'
exec up_UpdateFlightStatusBat @xml