sql for xml query 求助 3

Question

sqlxmlopenxmlquery

Answer 1

不要用DATETIME关键字 不然得到的结果是NULLif object_id('test') is not null
drop proc test 
go
create procedure test 
(
@xDailyConfig xml
)
as DECLARE @handel int;
EXEC sp_xml_preparedocument @handel output, @xDailyConfig
 SELECT * from OPENXML(@handel,'ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime1',3)
 WITH
 (
 [dateTime1] varchar(50) '.[1]'
 ) 
  EXEC sp_xml_removedocument @handel go exec test
 '
<ArrayOfScheduledTime>
   <ScheduledTime>
     <RecurrenceType>Everyday</RecurrenceType>
     <Stamp>
       <dateTime1>2000-01-01T09:00:00</dateTime1>
       <dateTime1>2000-01-01T13:00:00</dateTime1>
       <dateTime1>2000-01-01T19:00:00</dateTime1>
     </Stamp>
   </ScheduledTime>
 </ArrayOfScheduledTime>' /*dateTime1
--------------------------------------------------
2000-01-01T09:00:00
2000-01-01T13:00:00
2000-01-01T19:00:00(3 行受影响)
*/

Answer 2

if object_id('test') is not null
drop proc test 
go
create procedure test 
(
@xDailyConfig xml
)
as 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 go exec test
 '
<ArrayOfScheduledTime>
   <ScheduledTime>
     <RecurrenceType>Everyday</RecurrenceType>
     <Stamp>
       <dateTime>2000-01-01T09:00:00</dateTime>
       <dateTime>2000-01-01T13:00:00</dateTime>
       <dateTime>2000-01-01T19:00:00</dateTime>
     </Stamp>
   </ScheduledTime>
 </ArrayOfScheduledTime>' /*dateTime
--------------------------------------------------
2000-01-01T09:00:00
2000-01-01T13:00:00
2000-01-01T19:00:00(3 行受影响)
*/好吧 其实没问题。

Answer 3

DECLARE @xDailyConfig XML
 
SET @xDailyConfig='<ArrayOfScheduledTime>
   <ScheduledTime>
     <RecurrenceType>Everyday</RecurrenceType>
     <Stamp>
       <dateTime>2000-01-01T09:00:00</dateTime>
       <dateTime>2000-01-01T13:00:00</dateTime>
       <dateTime>2000-01-01T19:00:00</dateTime>
     </Stamp>
   </ScheduledTime>
 </ArrayOfScheduledTime>'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 go
 /*dateTime
--------------------------------------------------
2000-01-01T09:00:00
2000-01-01T13:00:00
2000-01-01T19:00:00(3 行受影响)
*/

Answer 4

DECLARE @xDailyConfig XMLSET @xDailyConfig='<ArrayOfScheduledTime>
  <ScheduledTime>
    <RecurrenceType>Everyday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T09:00:00</dateTime>
      <dateTime>2000-01-01T13:00:00</dateTime>
      <dateTime>2000-01-01T19:00:00</dateTime>
    </Stamp>
  </ScheduledTime>
</ArrayOfScheduledTime>'DECLARE @handel int;
EXEC sp_xml_preparedocument @handel output, @xDailyConfig
SELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime',3)
WITH(dateTime varchar(50) '.[1]') 

Answer 5

DECLARE @handel int;
EXEC sp_xml_preparedocument @handel output, @xDailyConfig
SELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime',3)
WITH(dateTime varchar(50) '.[1]') 
/*
2000-01-01T09:00:00
2000-01-01T13:00:00
2000-01-01T19:00:00
*/