使用 XPath/XQuery 过滤 XML 列上的 SQL 查询

Filter SQL queries on the XML column using XPath/XQuery

提问by Mats Fredriksson

I'm having a table with one XML column. I'd like to filter out the rows where a specific attribute in the XML match a string, essentially doing a WHERE or HAVING.

我有一个包含一个 XML 列的表。我想过滤掉 XML 中特定属性与字符串匹配的行，本质上是执行 WHERE 或 HAVING。

The table looks something like this

桌子看起来像这样

| id | xml |

And the XML something similar to

和 XML 类似的东西

<xml>
  <info name="Foo">
    <data .../>
  </info>
<xml>

I want to get all ids where the @name attribute matched a value.

我想获取@name 属性与值匹配的所有ID。

I have been able to do the following:

我已经能够做到以下几点：

SELECT id, xml.query('data(/xml/info/@name)') as Value
FROM Table1
WHERE CAST(xml.query('data(/xml/info/@name)') as varchar(1024)) = @match

But it's incredibly slow.

但它非常慢。

There must be a better way of filtering on the output of the query.

必须有更好的过滤查询输出的方法。

回答by Mats Fredriksson

Found it. Instead of using query() I should be using exist().

找到了。我应该使用exist()而不是使用query ()。

My query would then be

我的查询将是

SELECT id, xml.query('data(/xml/info/@name)') as Value
FROM Table1
WHERE xml.exist('/xml/info/[@name=sql:variable("@match")]') = 1