使用SQL Server 2005的XQuery选择具有特定属性值或缺少该属性的所有节点

Update: giving a much more thorough example.

The first two solutions offered were right along the lines of what I was trying to say not to do. I can't know location, it needs to be able to look at the whole document tree. So a solution along these lines, with /Books/ specified as the context will not work:

提供的前两个解决方案正是我试图说不要做的。我不知道位置，它需要能够查看整个文档树。因此，使用/ Books /指定为上下文的这些行的解决方案将不起作用：

SELECT x.query('.') FROM @xml.nodes('/Books/*[not(@ID) or @ID = 5]') x1(x)

Original question with better example:

Using SQL Server 2005's XQuery implementation I need to select all nodes in an XML document, just once each and keeping their original structure, but only if they are missing a particular attribute, or that attribute has a specific value (passed in by parameter). The query also has to work on the whole XML document (descendant-or-self axis) rather than selecting at a predefined depth.

使用SQL Server 2005的XQuery实现我需要选择XML文档中的所有节点，每个节点只保留一次并保持其原始结构，但前提是它们缺少特定属性，或者该属性具有特定值（通过参数传入）。查询还必须处理整个XML文档（后代或自身轴），而不是选择预定义的深度。

That is to say, each individual node will appear in the resultant document only if it and every one of its ancestors are missing the attribute, or have the attribute with a single specific value.

也就是说，每个单独的节点只有在它和它的每个祖先都缺少属性或者具有单个特定值的属性时才会出现在结果文档中。

For example:

If this were the XML:

如果这是XML：

    DECLARE @Xml XML
    SET @Xml =
    N'
<Library>
  <Novels>
    <Novel category="1">Novel1</Novel>
    <Novel category="2">Novel2</Novel>
    <Novel>Novel3</Novel>
    <Novel category="4">Novel4</Novel>
  </Novels>
  <Encyclopedias>
    <Encyclopedia>
      <Volume>A-F</Volume>
      <Volume category="2">G-L</Volume>
      <Volume category="3">M-S</Volume>
      <Volume category="4">T-Z</Volume>
    </Encyclopedia>
  </Encyclopedias>
  <Dictionaries category="1">
    <Dictionary>Webster</Dictionary>
    <Dictionary>Oxford</Dictionary>
  </Dictionaries>
</Library>
    '

A parameter of 1 for category would result in this:

类别的参数1将导致：

<Library>
  <Novels>
    <Novel category="1">Novel1</Novel>
    <Novel>Novel3</Novel>
  </Novels>
  <Encyclopedias>
    <Encyclopedia>
      <Volume>A-F</Volume>
    </Encyclopedia>
  </Encyclopedias>
  <Dictionaries category="1">
    <Dictionary>Webster</Dictionary>
    <Dictionary>Oxford</Dictionary>
  </Dictionaries>
</Library>

A parameter of 2 for category would result in this:

类别的参数2将导致：

<Library>
  <Novels>
    <Novel category="2">Novel2</Novel>
    <Novel>Novel3</Novel>
  </Novels>
  <Encyclopedias>
    <Encyclopedia>
      <Volume>A-F</Volume>
      <Volume category="2">G-L</Volume>
    </Encyclopedia>
  </Encyclopedias>
</Library>

I know XSLT is perfectly suited for this job, but it's not an option. We have to accomplish this entirely in SQL Server 2005. Any implementations not using XQuery are fine too, as long as it can be done entirely in T-SQL.

我知道XSLT非常适合这项工作，但它不是一个选择。我们必须在SQL Server 2005中完全实现这一点。任何不使用XQuery的实现都可以，只要它可以完全在T-SQL中完成。

2 个解决方案

#1

It's not clear for me from your example what you're actually trying to achieve. Do you want to return a new XML with all the nodes stripped out except those that fulfill the condition? If yes, then this looks like the job for an XSLT transform which I don't think it's built-in in MSSQL 2005 (can be added as a UDF: http://www.topxml.com/rbnews/SQLXML/re-23872_Performing-XSLT-Transforms-on-XML-Data-Stored-in-SQL-Server-2005.aspx).

从你的例子中我不清楚你实际上想要实现什么。是否要返回一个新的XML，除了满足条件的节点外，所有节点都被剥离了？如果是，那么这看起来像是XSLT转换的工作，我认为它不是MSSQL 2005中内置的（可以添加为UDF：http：//www.topxml.com/rbnews/SQLXML/re- 23872_Performing-XSLT的变换上，XML数据存储功能于SQL-Server的2005.aspx）。

If you just need to return the list of nodes then you can use this expression:

如果您只需要返回节点列表，则可以使用以下表达式：

//Book[not(@ID) or @ID = 5]

but I get the impression that it's not what you need. It would help if you can provide a clearer example.

但我觉得这不是你需要的。如果你能提供一个更清晰的例子，它会有所帮助。

Edit: This example is indeed more clear. The best that I could find is this:

编辑：这个例子确实更清楚。我能找到的最好的是：

SET @Xml.modify('delete(//*[@category!=1])')
SELECT @Xml

The idea is to delete from the XML all the nodes that you don't need, so you remain with the original structure and the needed nodes. I tested with your two examples and it produced the wanted result.

我们的想法是从XML中删除您不需要的所有节点，因此您将保留原始结构和所需节点。我测试了你的两个例子，它产生了想要的结果。

However modify has some restrictions - it seems you can't use it in a select statement, it has to modify data in place. If you need to return such data with a select you could use a temporary table in which to copy the original data and then update that table. Something like this:

但是修改有一些限制 - 似乎你不能在select语句中使用它，它必须在适当的位置修改数据。如果需要使用select返回此类数据，则可以使用临时表来复制原始数据，然后更新该表。像这样的东西：

INSERT INTO #temp VALUES(@Xml)
UPDATE #temp SET data.modify('delete(//*[@category!=2])')

Hope that helps.

希望有所帮助。

#2

The question is not really clear, but is this what you're looking for?

问题不是很清楚，但这正是你要找的吗？

DECLARE @Xml AS XML
        SET @Xml =
        N'
        <Books>
                <Book ID="1">Book1</Book>
                <Book ID="2">Book2</Book>
                <Book ID="3">Book3</Book>
                <Book>Book4</Book>
                <Book ID="5">Book5</Book>
                <Book ID="6">Book6</Book>
                <Book>Book7</Book>
                <Book ID="8">Book8</Book>
        </Books>
        '
DECLARE @BookID AS INT
SET @BookID = 5
DECLARE @Result AS XML

SET @result = (SELECT @xml.query('//Book[not(@ID) or @ID = sql:variable("@BookID")]'))
SELECT @result

#1