在 SQL Server XML 数据类型上使用 LIKE 语句 [英] Use a LIKE statement on SQL Server XML Datatype

问题描述

如果您有一个 varchar 字段，您可以轻松地执行 SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%' 以查看该列是否包含某个字符串.

If you have a varchar field you can easily do SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%' to see if that column contains a certain string.

你如何为 XML 类型做到这一点?

How do you do that for XML Type?

我有以下内容仅返回具有文本"节点的行，但我需要在该节点内进行搜索

I have the following which returns only rows that have a 'Text' node but I need to search within that node

select * from WebPageContent where data.exist('/PageContent/Text') = 1

推荐答案

你应该能够很容易地做到这一点:

You should be able to do this quite easily:

SELECT * 
FROM WebPageContent 
WHERE data.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE 'XYZ%'

.value 方法为您提供实际值，您可以将其定义为 VARCHAR() 返回，然后您可以使用 LIKE 语句进行检查.

The .value method gives you the actual value, and you can define that to be returned as a VARCHAR(), which you can then check with a LIKE statement.

请注意，这不会非常快.因此，如果您的 XML 中有某些字段需要大量检查，您可以:

Mind you, this isn't going to be awfully fast. So if you have certain fields in your XML that you need to inspect a lot, you could:

• 创建一个存储函数，该函数获取 XML 并以 VARCHAR() 形式返回您要查找的值
• 在你的表上定义一个新的计算字段来调用这个函数，并使它成为一个 PERSISTED 列

有了这个，您基本上可以将 XML 的某个部分提取"到一个计算字段中，使其持久化，然后您可以非常有效地搜索它(见鬼:您甚至可以索引该字段！).

With this, you'd basically "extract" a certain portion of the XML into a computed field, make it persisted, and then you can search very efficiently on it (heck: you can even INDEX that field!).

马克

这篇关于在 SQL Server XML 数据类型上使用 LIKE 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！