sql for xml query 3

sql for xml query 求助3
declare @x xml
   
select @x='<ArrayOfScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EverySaturday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T07:00:00</dateTime>
  <dateTime>2000-01-01T08:00:00</dateTime>
    </Stamp>
  </ScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EveryWednesday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T09:00:00</dateTime>
    </Stamp>
  </ScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EveryFriday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T09:00:00</dateTime>
  <dateTime>2000-01-01T09:20:00</dateTime>
  <dateTime>2000-01-01T09:40:00</dateTime>
    </Stamp>
  </ScheduledTime>
  <ScheduledTime>
    <RecurrenceType>EverySunday</RecurrenceType>
    <Stamp>
      <dateTime>2000-01-01T09:00:00</dateTime>
    </Stamp>
  </ScheduledTime>
</ArrayOfScheduledTime>'

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

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

/* 希望得到   统计每个RecurrenceType对其对应的dateTime的个数
EverySaturday     2
EveryWednesday    1
EveryFriday       3
EverySunday       1
*/


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

 SELECT T2.RecurrenceType,COUNT(T3.[dateTime])[dateTime] FROM
(SELECT CONVERT(XML,N.v.query('.'))C1 FROM @x.nodes('/ArrayOfScheduledTime/ScheduledTime')N(v))T1
OUTER APPLY(SELECT M.v.value('.','VARCHAR(100)')RecurrenceType FROM T1.C1.nodes('//RecurrenceType') M(v))T2
OUTER APPLY(SELECT L.v.value('.','VARCHAR(100)')[dateTime] FROM T1.C1.nodes('//dateTime') L(v))T3
GROUP BY T2.RecurrenceType

你参考下
------解决思路----------------------
用 XPath 的　count 就可以了
调试了老半天，路径一会用 / 一会用 // 

SELECT N.v.query('RecurrenceType').value('.','VARCHAR(20)') B ,
       N.v.query('count(Stamp//dateTime)').value('.','int') C
  FROM @x.nodes('/ArrayOfScheduledTime/ScheduledTime')N(v)

B                              C
-------------------- -----------
EverySaturday                  2
EveryWednesday                 1
EveryFriday                    3
EverySunday                    1