SQL Server XML 存在()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8467006/
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
SQL Server XML exist()
提问by joanna
I have some problems using the exist()and value()methods in SQL Server 2008.
我在使用SQL Server 2008 中的exist()和value()方法时遇到了一些问题。
My XML looks like this:
我的 XML 看起来像这样：
<?xml version="1.0" encoding="UTF-8"?>
<library>
<branches>
<branch>
<codelib>1</codelib>
<name>Campus</name>
</branch>
<branch>
<codelib>2</codelib>
<name>47th</name>
</branch>
<branch>
<codelib>3</codelib>
<name>Mall</name>
</branch>
</branches>
<books>
<book type="SF">
<codb>11</codb>
<title>Robots</title>
<authors>
<author>author1 robots</author>
<author>author2 robots</author>
</authors>
<price>10</price>
<stocks>
<branch codelib="1" amount="10"/>
<branch codelib="2" amount="5"/>
<branch codelib="4" amount="15"/>
</stocks>
<from>20</from>
<to>30</to>
</book>
<book type="poetry">
<codb>12</codb>
<title>Poetry book</title>
<authors>
<author>AuthorPoetry</author>
</authors>
<price>14</price>
<stocks>
<branch codelib="1" amount="7"/>
<branch codelib="2" amount="5"/>
</stocks>
<from>25</from>
<to>40</to>
</book>
<book type="children">
<codb>19</codb>
<title>Faitytales</title>
<authors>
<author>AuthorChildren</author>
</authors>
<price>20</price>
<stocks>
<branch codelib="1" amount="10"/>
<branch codelib="3" amount="55"/>
<branch codelib="4" amount="15"/>
</stocks>
<from>70</from>
<to>75</to>
</book>
<book type="literature">
<codb>19</codb>
<title>T</title>
<authors>
<author>A</author>
</authors>
<price>17</price>
<stocks>
<branch codelib="1" amount="40"/>
</stocks>
<from>85</from>
<to>110</to>
</book>
</books>
</library>
Given this XML, I have to write a SELECTclause that will use query(), value()and exist()2 times each, minimum. I can't even use query()and exist()in the same SELECT, as it appears that the WHEREclause has no effect whatsoever.
鉴于此 XML，我必须编写一个SELECT子句，该子句将使用query(),value()并且exist()每次最少使用2 次。我什query()至不能使用and exist()in the same SELECT，因为似乎该WHERE条款没有任何效果。
For example, I want to retrieve all the <branch>elements that are children of the book with the type SF, but the select statement
例如，我想检索<branch>类型为书的所有子元素SF，但选择语句
declare @genre varchar(15)
set @genre = 'SF'
SELECT XMLData.query('//branch') from TableA
WHERE XMLData.exist('//book[./@type = sql:variable("@genre")]') = 1
retrieves all the <branch>elements, not just the ones from the targeted book. I can't figure out what's wrong with my select. Also, I would appreciate a small example with query(), exist()and value()in the same select (is it possible to have nested select statements in sql xml?)
检索所有<branch>元素，而不仅仅是目标书籍中的元素。我无法弄清楚我的选择有什么问题。另外，我很欣赏一个带有query(),exist()并且value()在同一个选择中的小例子（是否可以在 sql xml 中嵌套选择语句？）
回答by marc_s
Well, your XPath expression here is the "culprit":
好吧，这里的 XPath 表达式是“罪魁祸首”：
query('//branch')
This says: select all<branch>nodes from the entire document. It is just doing what you're telling it to do, really....
这表示：从整个文档中选择所有<branch>节点。它只是做你告诉它做的事情，真的......
What's wrong with this query here??
这里的查询有什么问题？？
SELECT
XMLData.query('/library/books/book[@type=sql:variable("@genre")]//branch')
FROM dbo.TableA
That would retrieve all the <branch>subnodes for the <book>node that has type="SF"as an attribute....
这将检索具有属性<branch>的<book>节点的所有子节点type="SF"......
What are you trying to achieve with your query(), exist()and value()all in the same statement?? Quite possibly, it can be done a lot easier....
你想用你的query(),exist()和value()所有在同一个声明中实现什么？很可能，它可以更容易地完成......
Also: I think you're misinterpreting what .exist()in SQL Server XQuery does. If you have your statement here:
另外：我认为您误解了.exist()SQL Server XQuery 的作用。如果您在这里发表声明：
SELECT (some columns)
FROM dbo.TableA
WHERE XMLData.exist('//book[@type = sql:variable("@genre")]') = 1
you're basically telling SQL Server to retrieve all rows from dbo.TableAwhere the XML stored in XMLDatacontains a <book type=.....>node - you are selecting rows from the table - NOTapplying a selection to the XMLDatacolumn's content...
您基本上是在告诉 SQL Server 从dbo.TableA存储的 XMLXMLData包含<book type=.....>节点的位置检索所有行-您是从表中选择行-而不是将选择应用于XMLData列的内容...
回答by Asher
The XML you supplied does not lend itself to an existstatement. If you had multiple XML statements and needed to find the one where it contained some value, then the statement would have been more relevant.
您提供的 XML 不适合exist声明。如果您有多个 XML 语句并且需要找到其中包含某个值的那个语句，那么该语句将更加相关。
The whereclause you supplied just checks if the condition exists and if it does, selects all the brancheselements, not just the one where the condition is true.
For example, the following (obviously) does not return anything:
where您提供的子句仅检查条件是否存在，如果存在，则选择所有branches元素，而不仅仅是条件为真的元素。例如，以下（显然）不返回任何内容：
SELECT @xmldata.query('//branch') from TableA
WHERE @xmldata.exist('//book[./@type = "BLAH"]') = 1
But here is something to show you can use all three in one select statement.
但这里有一些东西表明您可以在一个 select 语句中使用所有三个。
SELECT T.c.query('./title').value('.', 'varchar(250)') as title,
T.c.exist('.[@type eq "SF"]') as IsSF
from @xmldata.nodes('//book') T(c)
回答by NuckingFuts
It's more efficient with XML to filter using the cross apply operator to filter to the required node then select the query from the returned nodes. To query the child nodes you also need to include the root . in the query so in this case .//branch rather than //branch.
使用交叉应用运算符过滤到所需的节点，然后从返回的节点中选择查询，使用 XML 进行过滤会更有效。要查询子节点，您还需要包含 root 。在查询中所以在这种情况下 .//branch 而不是 //branch。
declare @genre varchar(15) = 'SF'
select l.query('.//branch') from TableA
cross apply XmlData.nodes('library/books/book[@type=sql:variable("@genre")]') n (l)
You can still add the exists clause if you want but this will actually add additional unnecessary overhead
如果需要，您仍然可以添加exists子句，但这实际上会增加额外的不必要的开销
WHERE XMLData.exist('//book[./@type = sql:variable("@genre")]') = 1
Hope this helps. D
希望这可以帮助。D
