使用XQuery访问SQLXML列中的子节点

Question

我有一个数据库，其中有一个带有XML列的表。XML数据有一堆子节点，如下所示：

<test>
  <result id="1234">
    <data elementname="Message">some error message</data>
    <data elementname="Cat">Cat01</data>
    <data elementname="Type">WARNING</data>
  </result>
  <result id="5678">
    <data elementname="Message">some error message</data>
    <data elementname="Cat">Cat01</data>
    <data elementname="Type">WARNING</data>
  </result>
</test>

Cat元素可以有许多不同的值。我正在尝试在这些数据上创建报告，所以我想做的一件事是通过我们的数据获得所有类别的列表。这是我的问题：

Select Id, XmlData.query('/test/result/data[@elementname = ''Cat''] ')  AS  Message
From Table
WHERE XmlData.exist('/test/result/data[@elementname = ''Cat'']') = 1
ORDER BY FriendlyName

这正确地获得了我的表中具有这种分类类型的所有行(在同一个表中还会有其他没有该元素的结果)，但是对于每个表记录，所有的类别都组合到一列中：

Id1, <data elementname="Cat">Cat01</data><data elementname="Cat">Cat01</data>
Id2, <data elementname="Cat">Cat01</data><data elementname="Cat">Cat01</data>

我包含了Id列，因此很容易看到数据来自哪里，主要问题是我只能让它连接每一行的值-我需要每个数据元素都有自己的行，然后可能对结果执行Select Distinct。

有什么办法可以做到这一点吗？

谢谢

Answer 1

总是在你发布你的问题后的谷歌....

我想我在这里找到了答案：http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

SELECT  DISTINCT  cref.value('(text())[1]', 'varchar(50)') as Cat
FROM   
      SGIS CROSS APPLY 
      Data.nodes('/test/result') AS Results(rref) CROSS APPLY
      rref.nodes('data[@elementname = ''Cat'']') AS Categories(cref)  

关键似乎是交叉应用关键字