XML 操作，列转行 (自各儿做留念，送分给大家)

XML 操作，列转行 (自己做留念，送分给大家)

SQL code

-- =============================================-- Author:        gguozhenqian-- Create date: 2012-03-12-- Description:    <Description,,>-- =============================================CREATE PROCEDURE up_UpdateFlightStatusBat    @Xml xmlASBEGIN          ;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 #flightStatusEND--测试  DECLARE @xml xmlSET @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