使用SQL Server XML数据类型

Question

我有一个具有XML字段的表。它包含的典型XML是：

<things>
  <Fruit>
    <imageId>39</imageId>
    <title>Apple</title>
  </Fruit>
  <Fruit>
    <imageId>55</imageId>
    <title>Pear</title>
  </Fruit>
  <Fruit>
    <imageId>76</imageId>
    <title>Grape</title>
  </Fruit>
</things>

在我的表中，我大约有50行，我只关心两个字段，即omId（int主键）和omText（我的xml数据）。

我想要实现的是
一种表达方式，涉及整个表中的所有xml数据…给我所有标题为X的xmlElements。或者给我计数所有使用imageId为55的项目。

我正在使用XML数据类型VALUE和QUERY函数来检索数据。

   select omID,
   omText.query('/things/Fruit')
   ,cast('<results>' + cast(omText.query('/things/Fruit') as varchar(max)) + '</results>' as xml) as Value
   from dbo.myTable
   where omText.value('(/things/Fruit/imageId)[1]', 'int') = 76

仅在我要搜索的ID是文档中的第一个ID的情况下才有效。它似乎并没有搜索所有的xml。

从根本上说，结果集返回表中的每个条目一行，我想我需要为每个匹配的元素有一行…不确定如何为此写一个分组依据。

我开始觉得我正在使这个工作变得比需要做的难。

Answer 1

我想要实现的是一种表达方式，跨整个表中的所有xml数据…给我所有标题为X的xmlElements。

不知道我是否完全理解您的问题-还是您在寻找这个？您将获取所有/ things /
Fruit元素一个“节点”，并将它们与myTable中的“基础数据”交叉连接-结果将是XML数据字段中每个XML元素一行：

select 
   omID,
   T.Fruit.query('.')
from 
   dbo.myTable
cross apply
   omText.nodes('/things/Fruit') as T(Fruit)
where 
   T.Fruit.value('(title)[1]', 'varchar(50)') = 'X'

或给我计数使用imageId为55的所有项目的数量。

select 
   count(*)
from 
   dbo.myTable
cross apply
   omText.nodes('/things/Fruit') as T(Fruit)
where 
   T.Fruit.value('(imageId)[1]', 'int') = 55

那是您要找的东西吗？