SQL Server XML 存在() [英] SQL Server XML exist()

问题描述

我在使用 SQL Server 2008 中的 exist() 和 value() 方法时遇到了一些问题.

I have some problems using the exist() and value() methods in SQL Server 2008.

我的 XML 如下所示:

My XML looks like this:

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

给定这个 XML，我必须编写一个 SELECT 子句，它将使用 query()、value() 和 exist() 每次 2 次，最少.我什至不能在同一个 SELECT 中使用 query() 和 exist()，因为看起来 WHERE 子句没有任何作用.

Given this XML, I have to write a SELECT clause 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 WHERE clause has no effect whatsoever.

例如，我想检索所有 <branch> 元素，这些元素是具有 SF 类型的书的子元素，但是选择语句

For example, I want to retrieve all the <branch> elements that are children of the book with the type SF, but the select statement

  declare @genre varchar(15)
  set @genre = 'SF'
  SELECT XMLData.query('//branch') from TableA
  WHERE XMLData.exist('//book[./@type = sql:variable("@genre")]') = 1

检索所有 元素，而不仅仅是目标书籍中的元素.我无法弄清楚我的选择有什么问题.另外，我希望在同一个选择中使用 query()、exist() 和 value() 的一个小例子(是否有可能在 sql xml 中有嵌套的 select 语句?)

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

推荐答案

好吧，这里的 XPath 表达式是罪魁祸首":

Well, your XPath expression here is the "culprit":

query('//branch')

这表示:从整个文档中选择所有 节点.它只是做你告诉它做的事情，真的......

This says: select all <branch> nodes from the entire document. It is just doing what you're telling it to do, really....

这里的查询有什么问题??

What's wrong with this query here??

SELECT 
    XMLData.query('/library/books/book[@type=sql:variable("@genre")]//branch')
FROM dbo.TableA

这将检索 节点的所有 子节点，该节点将 type="SF" 作为属性....

That would retrieve all the <branch> subnodes for the <book> node that has type="SF" as an attribute....

你想用你的 query()、exist() 和 value() 在同一个语句中实现什么?很可能，它可以做得更容易....

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

另外:我认为您误解了 SQL Server XQuery 中 .exist() 的作用.如果您在此处发表声明:

Also: I think you're misinterpreting what .exist() in SQL Server XQuery does. If you have your statement here:

 SELECT (some columns)
 FROM dbo.TableA
 WHERE XMLData.exist('//book[@type = sql:variable("@genre")]') = 1

您基本上是在告诉 SQL Server 从 dbo.TableA 检索所有行，其中存储在 XMLData 中的 XML 包含 <book type=.....> 节点 - 您正在从表中选择行 - NOT 将选择应用于 XMLData 列的内容...

you're basically telling SQL Server to retrieve all rows from dbo.TableA where the XML stored in XMLData contains a <book type=.....> node - you are selecting rows from the table - NOT applying a selection to the XMLData column's content...

这篇关于SQL Server XML 存在()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！