频道栏目
IT货架 > > 正文
XML 操作,列转行 (自各儿做留念,送分给大家)
网友分享于:Jun 12, 2018 10:44:04 PM    来源: IT货架   
XML 操作,列转行 (自己做留念,送分给大家)
SQL code

  
-- =============================================
-- 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



------解决方案--------------------
真小气,总共才20分。!!!!!!!不过,学习到东西是好的。哈哈!分数不重要。
------解决方案--------------------
谢谢分享!
------解决方案--------------------
感谢分享.
------解决方案--------------------

广告服务联系QQ:1134687142 | 网站地图

版权所有: IT货架- 内容来自互联网,仅供用于技术学习,请遵循相关法律法规. 京ICP备11030978号-1