在sql2005中对不同XML的解析.

--联机帮助:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/udb9/html/689297f3-adb0-4d8d-bf62-cfda26210164.htm
--eg1.
DECLARE @idoc int;
DECLARE @doc varchar(4000);
SET @doc ='
 
 
 
	
  
  
   
    2007-11-22   09:27:04 
  
  
	
  
  
   
    MOD 
  
  
	
  
  
   
    A 
  
  
	
  
  
		
   
   
    
     21 
   
   
		
   
   
    
     E145 
   
   
	
  
  

 
 
'
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc  --形成各节点（元素、属性、文本和注释等）的树状表示形式 返回一个句柄
 SELECT * FROM OPENXML (@Idoc, '/FLIGHT/FLIGHTINFOR',2)WITH (
			dt DATETIME  '../DATETIME'
			,METHOD varchar(20) '../METHOD'
			,tp char(1)'../TYPE'
			,FLIGHTATTR  int  'FLIGHTATTR'
			,AIRCRAFTTYPE varchar(10)'AIRCRAFTTYPE'
)
exec sp_xml_removedocument @Idoc
--结果:
/*
dt	METHOD	tp	FLIGHTATTR	AIRCRAFTTYPE
2007-11-22 09:27:04.000	MOD	A	21	E145
*/

--eg2.
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='

 
 

  
  
   
   
   
      
    
    
      
    
    
   
   
   

  
  

  
  
   
   
   
      
    
    
   
   
   

  
  

 
 '
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (CustomerID2  varchar(10) '../../@CustomerID' ,
				ContactName varchar(20)  '../../@ContactName',
				OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity')
--结果:
/*
CustomerID2	ContactName	OrderID	CustomerID	OrderDate	ProdID	Qty
VINET	Paul Henriot	10248	VINET	1996-07-04 00:00:00.000	11	12
VINET	Paul Henriot	10248	VINET	1996-07-04 00:00:00.000	42	10
LILAS	Carlos Gonzlez	10283	LILAS	1996-08-16 00:00:00.000	72	3
*/

--eg3.
DECLARE @idoc int;
DECLARE @doc varchar(4000);
SET @doc ='
 
 
 

  
  
   
   sdfsdf
  
  
	
  
  
   
    2007-11-22   09:27:04 
  
  
	
  
  
   
    MOD 
  
  
	
  
  
   
    MOD23 
  
  
	
  
  
   
    MOD22 
  
  
	
  
  
   
    MOD2 5
  
  
	
  
  
   
    A 
  
  
	
  
  
		
   
   
    
     21 
   
   
		
   
   
    
     E145 
   
   
		
   
   
    
     MOD22 
   
   
			
   
   
			
    
    
     
     dfsdfs
    
    
				
			
   
   
	
  
  

 
 
'
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc  --形成各节点（元素、属性、文本和注释等）的树状表示形式 返回一个句柄
 SELECT * FROM OPENXML (@Idoc, '/FLIGHT',2)WITH (
--			DATETIME2 DATETIME  '../DATETIME'
			METHOD varchar(200) '..//METHOD/'
--			,TYPE char(1) '../TYPE'
--			,FLIGHTATTR  int  'FLIGHTATTR'
--			,AIRCRAFTTYPE varchar(10)'AIRCRAFTTYPE'
--,METHOD2 varchar(10) 'METHOD'
)
exec sp_xml_removedocument @Idoc

--结果:
/*
sdfsdf  2007-11-22   09:27:04   MOD   MOD23   MOD22   MOD2 5  A   21   E145   MOD22  dfsdfs
/*