在 SQL Server XML 数据类型上使用 LIKE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1832987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use a LIKE statement on SQL Server XML Datatype
提问by Jon
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.
如果您有一个 varchar 字段，您可以轻松SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%'查看该列是否包含某个字符串。
How do you do that for XML Type?
你如何为 XML 类型做到这一点？
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
采纳答案by marc_s
You should be able to do this quite easily:
你应该能够很容易地做到这一点：
SELECT *
FROM WebPageContent
WHERE data.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE 'XYZ%'
The .valuemethod 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.
该.value方法为您提供实际值，您可以将其定义为 VARCHAR() 返回，然后您可以使用 LIKE 语句进行检查。
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 中有某些字段需要大量检查，您可以：
- create a stored function which gets the XML and returns the value you're looking for as a VARCHAR()
- define a new computed field on your table which calls this function, and make it a PERSISTED column
- 创建一个存储函数，它获取 XML 并返回您要查找的值作为 VARCHAR()
- 在调用此函数的表上定义一个新的计算字段，并使其成为 PERSISTED 列
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!).
有了这个，您基本上可以将 XML 的某个部分“提取”到一个计算字段中，使其持久化，然后您可以非常有效地搜索它（见鬼：您甚至可以索引该字段！）。
Marc
马克
回答by Squazz
Yet another option is to cast the XML as nvarchar, and then search for the given string as if the XML vas a nvarchar field.
另一种选择是将 XML 转换为 nvarchar，然后搜索给定的字符串，就像 XML 与 nvarchar 字段一样。
SELECT *
FROM Table
WHERE CAST(Column as nvarchar(max)) LIKE '%TEST%'
I love this solution as it is clean, easy to remember, hard to mess up, and can be used as a part of a where clause.
我喜欢这个解决方案，因为它干净、易于记忆、不易弄乱，并且可以用作 where 子句的一部分。
EDIT: As Cliff mentions it, you could use:
编辑：正如 Cliff 所提到的，您可以使用：
...nvarchar if there's characters that don't convert to varchar
...nvarchar 如果有不转换为 varchar 的字符
回答by Carl Onager
Another option is to search the XML as a string by converting it to a string and then using LIKE. However as a computed column can't be part of a WHERE clause you need to wrap it in another SELECT like this:
另一种选择是通过将 XML 转换为字符串然后使用 LIKE 来搜索作为字符串的 XML。但是，由于计算列不能成为 WHERE 子句的一部分，因此您需要将其包装在另一个 SELECT 中，如下所示：
SELECT * FROM
(SELECT *, CONVERT(varchar(MAX), [COLUMNA]) as [XMLDataString] FROM TABLE) x
WHERE [XMLDataString] like '%Test%'
回答by Jon
This is what I am going to use based on marc_s answer:
这是我将根据 marc_s 答案使用的内容：
SELECT
SUBSTRING(DATA.value('(/PAGECONTENT/TEXT)[1]', 'VARCHAR(100)'),PATINDEX('%NORTH%',DATA.value('(/PAGECONTENT/TEXT)[1]', 'VARCHAR(100)')) - 20,999)
FROM WEBPAGECONTENT
WHERE COALESCE(PATINDEX('%NORTH%',DATA.value('(/PAGECONTENT/TEXT)[1]', 'VARCHAR(100)')),0) > 0
Return a substring on the search where the search criteria exists
在搜索条件存在的搜索中返回一个子字符串
