使用 SQL Server XML 数据类型 [英] Working with the SQL Server XML data type

问题描述

我有一个包含 XML 字段的表.它包含的典型 XML 是;

<东西><水果><imageId>39</imageId><title>苹果</title></水果><水果><imageId>55</imageId><title>梨</title></水果><水果><imageId>76</imageId><title>葡萄</title></水果></东西>

在我的表中有大约 50 行，我只关心两个字段，omId(int 主键)和 omText(我的 xml 数据).

我想要实现的是 一种说法，在整个表格中的所有 xml 数据中......给我所有的 xmlElements 标题是 X.或者给我一个使用 imageId 为 55 的所有项目的计数.

我使用 XML 数据类型 VALUE 和 QUERY 函数来检索数据.

 选择 omID，omText.query('/东西/水果'),cast('<results>' + cast(omText.query('/things/Fruit') as varchar(max)) + '</results>' as xml) as Value来自 dbo.myTable其中 omText.value('(/things/Fruit/imageId)[1]', 'int') = 76

这仅适用于我正在搜索的 ID 是文档中的第一个 ID.它似乎没有搜索所有的 xml.

从根本上说，结果集会为 TABLE 中的每个条目返回一行，而我认为我需要为每个匹配的 ELEMENT 分配一行...不确定如何开始为此编写分组依据.

我开始觉得我让这件事变得比它需要的更难......想法和;请提出想法.

解决方案

<块引用>

我想要实现的是一种说法，在所有整个表中的xml数据...给我所有的xmlElements其中标题是 X.

不确定我是否完全理解您的问题 - 或者您正在寻找这个?您将获取所有/things/Fruit 元素作为节点"并将它们与 myTable 中的基本数据"交叉连接 - 结果将是您的 XML 数据字段中的每个 XML 元素一行:

选择OMID，T.Fruit.query('.')从dbo.myTable交叉申请omText.nodes('/things/Fruit') as T(Fruit)在哪里T.Fruit.value('(title)[1]', 'varchar(50)') = 'X'

<块引用><块引用>

或者给我一个使用 imageId 为 55 的所有项目的计数.

选择数数(*)从dbo.myTable交叉申请omText.nodes('/things/Fruit') as T(Fruit)在哪里T.Fruit.value('(imageId)[1]', 'int') = 55

这就是你要找的吗?

马克

I've got a table which has a XML field. The typical XML it contains is;

<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>

In my table i've got around 50 rows, i'm only concerned with two fields, omId (int primary key) and omText (my xml data).

What i'm trying to achieve is a way of saying, across all xml data in the whole table... give me all of the xmlElements where the title is X. Or give me a count of all items that use an imageId of 55.

I'm using the XML data type VALUE and QUERY functions to retrieve the data.

   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

Which only works where the id i'm searching for is the first one in the document. It doesn't seem to search all of the xml.

Fundamentally the resultset comes back with one row for each entry in the TABLE, wheras i think i need to have one row for each matched ELEMENT... Not exactly sure how to start writing a group-by for this tho.

I'm starting to feel like i'm making this harder than it needs to be...... thoughts & ideas please.

解决方案

What i'm trying to achieve is a way of saying, across all xml data in the whole table... give me all of the xmlElements where the title is X.

Not sure if I totally understood your question here - or are you looking for this? You would grab all the /things/Fruit elements a "nodes" and cross join them against your "base data" in the myTable - the result would be one row per XML element in your XML data field:

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'

Or give me a count of all items that use an imageId of 55.

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

Is that what you're looking for?

Marc

这篇关于使用 SQL Server XML 数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！