如何对以下代码进行分组/在XML数据中进行分组

Question

select               first name
              ,last name
               ,employeeID
                ,CASE
                     WHEN CONVERT(xml,detail).value('count(//education/item)','nvarchar(50)')=0 AND
                      CONVERT(xml,detail).value('(//preventive/itemValue/text())[1]','nvarchar(50)') LIKE '%Employee refuses to participate %' THEN
                           'AAA-1'

                       WHEN CONVERT(xml,detail).value('count(//education/item)','nvarchar(50)') =0 AND
                        CONVERT(xml,detail).value('count(//preventive/itemValue)','nvarchar(50)')=0 THEN
                            'AAA-2'
                        ELSE
                             'AAA-3'
                        END val

                        ,Start Date
                        ,End Date

FROM (HAS MULTIPLE JOINS HERE & CONDITION )



GROUP BY       first name
              ,last name
               ,employeeID
                ,CASE
                     WHEN CONVERT(xml,detail).value('count(//education/item)','nvarchar(50)')=0 AND
                      CONVERT(xml,detail).value('(//preventive/itemValue/text())[1]','nvarchar(50)') LIKE '%Employee refuses to participate %' THEN
                           'AAA-1'

                       WHEN CONVERT(xml,detail).value('count(//education/item)','nvarchar(50)') =0 AND
                        CONVERT(xml,detail).value('count(//preventive/itemValue)','nvarchar(50)')=0 THEN
                            'AAA-2'
                        ELSE
                             'AAA-3'
                        END 

                        ,Start Date
                        ,End Date

我的代码执行得非常好，但是当我编写group by子句时，它抛出一个错误，指出XML数据类型不能分组。我知道我们可以通过用户定义的函数来实现。谁能让我知道如何完成这项任务。我需要在代码中使用group by子句。我使用的是SQL Server 2005/2008。

Answer 1

可以使用cross apply在子查询中查询XML，然后在字段列表和group by子句中使用子查询返回的值。

declare @T table(XMLCol xml)

insert into @T values
('<root>1</root>'),
('<root>2</root>'),
('<root>3</root>'),
('<root>3</root>')

select X.Value
from @T
  cross apply (select T.N.value('.', 'int')
               from XMLCol.nodes('root') as T(N)) as X(Value)
group by X.Value

结果：

Value
-----
1
2
3