sqlforxmlquerysample

sample 1:

declare @x xml
   
select @x='
  
    EveryMonday
    
      2000-01-01T19:30:00
    
  
  
    EveryThursday
    
      2000-01-01T19:30:00
    
  
  
    EverySunday
    
      2000-01-01T19:30:00
    
  
'

SELECT N.v.value('.' , 'VARCHAR(100)')B 
FROM @x.nodes('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')N(v)

/*
EveryMonday
EveryThursday
EverySunday
*/

select @x.query('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')
/*
EveryMonday
EveryThursday
EverySunday
*/

select o.value('RecurrenceType[1]','varchar(20)') 'RecurrenceType'
 from (select @x 'x') t
 cross apply x.nodes('/ArrayOfScheduledTime/ScheduledTime') x(o)
 
/*
RecurrenceType
--------------------
EveryMonday
EveryThursday
EverySunday
 
(3 行受影响)
*/

DECLARE @handel int;
EXEC sp_xml_preparedocument @handel output, @x
SELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime',2)
WITH(RecurrenceType varchar(50)) 

EXEC sp_xml_removedocument @handel

/**************结果*****************
EveryMonday
EveryThursday
EverySunday
*/

sample2:

DECLARE @xDailyConfig XML
set @xDailyConfig='
  
    Everyday
    
      2000-01-01T09:00:00
      2000-01-01T13:00:00
      2000-01-01T19:00:00
    
  
'

SELECT N.v.value('.' , 'VARCHAR(100)')B 
FROM @xDailyConfig.nodes('/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime')N(v)

/*
2000-01-01T09:00:00
2000-01-01T13:00:00
2000-01-01T19:00:00
*/

DECLARE @handel int;
EXEC sp_xml_preparedocument @handel output, @xDailyConfig
SELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime',3)
WITH(dateTime varchar(50) '.[1]') 

EXEC sp_xml_removedocument @handel

/*
2000-01-01T09:00:00
2000-01-01T13:00:00
2000-01-01T19:00:00
*/

来源：

SQL特殊语句的笔记

http://www.2cto.com/database/201205/133329.html

说明，使用 openxml后，一定要记得用 sp_xml_removedocument 释放xml document资源。

(结束)