sql for xml 查询_Sql Server

sql for xml 查询求助
--Question1:

declare @x xml

select @x='<ArrayOfScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EveryMonday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T19:30:00</dateTime>
    </Stamp>
  </ScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EveryThursday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T19:30:00</dateTime>
    </Stamp>
  </ScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EverySunday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T19:30:00</dateTime>
    </Stamp>
  </ScheduledTime>
</ArrayOfScheduledTime>'

--select @x.query('/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime')

select @x.query('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')

/*
得到
<RecurrenceType>EveryMonday</RecurrenceType>
<RecurrenceType>EveryThursday</RecurrenceType>
<RecurrenceType>EverySunday</RecurrenceType>

我希望得到
EveryMonday
EveryThursday
EverySunday
*/

------解决思路----------------------


--select @x.query('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')

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

------解决思路----------------------
你试下

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

------解决思路----------------------


declare @x xml
  
select @x='<ArrayOfScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EveryMonday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T19:30:00</dateTime>
    </Stamp>
  </ScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EveryThursday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T19:30:00</dateTime>
    </Stamp>
  </ScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EverySunday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T19:30:00</dateTime>
    </Stamp>
  </ScheduledTime>
</ArrayOfScheduledTime>'

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 行受影响)
*/