sql for xml query 求助3

Question

xmlsqlselect

Answer 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
你参考下

Answer 2

用 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