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

[振乾]

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

[trunjun]

真小气，总共才20分。！！！！！！！不过，学习到东西是好的。哈哈！分数不重要。

[叶子]

谢谢分享！

[dawugui]

感谢分享.

[挨踢直男]

[wdq85]

多谢分享

[flyfly2008]

有好东西，拿出来分享，赞一个

[EnForGrass]

谢谢分享

[振乾]

declare @Xml xml 
set  @Xml=N'<es><e EcgID="0cef0895-de0e-4978-9470-46ac194aea43" Compressed="asasf" CompressedLen="74249" Ratio="0.520362752037677" CompressSpeed="87" DecompressSpeed="105" /></es>'
SELECT M.a.value('@EcgID', 'uniqueidentifier') AS ecgID,
			M.a.value('@Compressed', 'varchar(max)') AS compressed,
			M.a.value('@CompressedLen', 'int') AS compressedLen,
			M.a.value('@Ratio', 'float') AS ratio,
			M.a.value('@CompressSpeed', 'float') AS compressSpeed,
			M.a.value('@DecompressSpeed', 'float') AS decompressSpeed
	 FROM   @XML.nodes('/es/e') M(a)  
	 

xml中 属性的大小写 一定要和@字段的 一模一样，不然获取的值为空。