使用 SQL Server XML 数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1212504/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Working with the SQL Server XML data type
提问by GordonB
I've got a table which has a XML field. The typical XML it contains is;
我有一个包含 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>
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).
在我的表中有大约 50 行，我只关心两个字段，omId（int 主键）和 omText（我的 xml 数据）。
What i'm trying to achieve isa 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.
我想要实现的是一种说法，在整个表中的所有 xml 数据中......给我所有标题为 X 的 xmlElements。或者给我一个使用 imageId 为 55 的所有项目的计数.
I'm using the XML data type VALUE and QUERY functions to retrieve the data.
我正在使用 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
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.
这只适用于我正在搜索的 id 是文档中的第一个。它似乎没有搜索所有的 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.
从根本上说，结果集会为 TABLE 中的每个条目返回一行，而我认为我需要为每个匹配的 ELEMENT 分配一行……不确定如何开始为此编写分组依据。
I'm starting to feel like i'm making this harder than it needs to be...... thoughts & ideas please.
我开始觉得我让这件事变得比它需要的更难......请提供想法和想法。
回答by marc_s
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.
我想要实现的是一种说法，在整个表中的所有 xml 数据中......给我标题为 X 的所有 xmlElements。
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:
不确定我是否完全理解您的问题 - 或者您正在寻找这个？您将获取所有 /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'
Or give me a count of all items that use an imageId of 55.
或者给我一个使用 imageId 为 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
马克
