SQL Server 2008 XPath [英] SQL Server 2008 XPath

问题描述

我们正在尝试根据我们提供的值过滤一组 XML.

We're trying to filter a set of XML based on a value we provide.

我们在数据库的 XML 字段中有以下 XML，如果通过数字5052095050830"，我们需要在 XML 中找到这个特定节点.我们提供的号码可能存在任意多次.

We have the following XML in an XML field with our database, and if passing through the number "5052095050830", we need to find this specific node in the XML. The number we provide may exist any number of times.

任何机构都可以提供一些示例 SQL 来提供帮助吗?

Can any body provide some example SQL to assist?

谢谢

<Attributes>
  <ProductVariantAttribute ID="4387">
    <ProductVariantAttributeValue>
      <Value>5052095050830</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="9999">
    <ProductVariantAttributeValue>
      <Value>5052095050830</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4388">
    <ProductVariantAttributeValue>
      <Value>104401330A</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4389">
    <ProductVariantAttributeValue>
      <Value>6905</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4390">
    <ProductVariantAttributeValue>
      <Value>6906</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4391">
    <ProductVariantAttributeValue>
      <Value>Monday, October 27, 2008</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
</Attributes>

推荐答案

您可以使用 .exist() 方法 - 像这样:

You can use the .exist() method - something like this:

SELECT 
(list of columns) 
FROM
dbo.YourTable
WHERE
YourXmlColumn.exist('//Value[text()="5052095050830"]') = 1

这会根据您提供的特定值进行检查.您可以越精确地定义期望找到该值的 XPath，您的性能就越好.

This checks against that particular value you've supplied. The more precisely you can define the XPath where that value is expected to be found, the better for your performance.

YourXmlColumn.exist('//Value[text()="5052095050830"]') = 1

非常糟糕 - 它查看 XML 中任何位置的每个 节点以找到该值.

is pretty bad - it looks into every single <Value> node anywhere in the XML to find that value.

像这样:

YourXmlColumn.exist('/Attributes/ProductVariantAttribute/ProductVariantAttributeValue/Value[text()="5052095050830"]') = 1

会更加专注，因此性能会更好 - 但它只会那些由 XPath 语句定义的特定节点

would be much more focused and thus much better for performance - but it would only those those particular nodes defined by that very XPath statement

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