对 SQLServerXML 数据类型使用 LIKE 语句

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

如何为 XMLType 做到这一点？

下面的代码只返回具有“ Text”节点的行，但是我需要在该节点内搜索

select * from WebPageContent where data.exist('/PageContent/Text') = 1
151146 次浏览
小开
最佳答案

You should be able to do this quite easily:

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

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.

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:

  • 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

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!).

Marc

小开

This is what I am going to use based on marc_s answer:

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

小开

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:

SELECT * FROM
(SELECT *, CONVERT(varchar(MAX), [COLUMNA]) as [XMLDataString] FROM TABLE) x
WHERE [XMLDataString] like '%Test%'
小开

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.

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.

This might not be the best performing solution, so think twice before deplying it to production. It is however very usefull for a quick debug session, which is where I mostly use it.

EDIT: As Cliff mentions it, you could use:

...nvarchar if there's characters that don't convert to varchar


提交答案