Использовать инструкцию LIKE для SQL Server XML Тип данных

Если у вас есть поле varchar, вы можете легко сделать SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%', чтобы увидеть, содержит ли этот столбец определенную строку.

Как вы это делаете для типа XML?

У меня есть следующее, которое возвращает только строки с текстом node, но мне нужно искать в нем node

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

Ответы

Ответ 1

Вы можете сделать это довольно легко:

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

Метод .value дает вам фактическое значение, и вы можете определить, что оно будет возвращено как VARCHAR(), которое вы можете проверить с помощью инструкции LIKE.

Помните, это не будет ужасно быстро. Поэтому, если у вас есть определенные поля в вашем XML, которые вам нужно проверить много, вы можете:

  • создать хранимую функцию, которая получает XML и возвращает значение, которое вы ищете как VARCHAR()
  • определить новое вычисленное поле в вашей таблице, которое вызывает эту функцию, и сделать его столбцом PERSISTED

При этом вы в основном "извлекаете" определенную часть XML в вычисленное поле, сохраняете его, а затем можете очень эффективно искать его (черт: вы можете даже индексировать это поле!).

Марк

Ответ 2

Еще один вариант заключается в том, чтобы передать XML как nvarchar, а затем выполнить поиск данной строки, как будто XML vas представляет собой поле nvarchar.

SELECT * 
FROM Table
WHERE CAST(Column as nvarchar(max)) LIKE '%TEST%'

Мне нравится это решение, так как оно чистое, легко запоминающееся, трудно перепутаться и может использоваться как часть предложения where.

EDIT: Как говорит Клифф, вы можете использовать:

... nvarchar, если символы, которые не преобразуются в varchar

Ответ 3

Другим вариантом является поиск XML в виде строки путем преобразования его в строку, а затем с использованием LIKE. Однако, поскольку вычисленный столбец не может быть частью предложения WHERE, вам нужно обернуть его другим SELECT следующим образом:

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

Ответ 4

Это то, что я собираюсь использовать, основываясь на ответе 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

Вернуть подстроку в поиске, где существуют критерии поиска