Użyj instrukcji LIKE w typie danych XML programu SQL Server

87

Jeśli masz pole varchar, możesz łatwo SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%'sprawdzić, czy ta kolumna zawiera określony ciąg.

Jak to zrobić dla typu XML?

Mam następujący, który zwraca tylko wiersze, które mają węzeł „Tekst”, ale muszę wyszukiwać w tym węźle

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

— Jon
źródło

70

Powinieneś być w stanie to zrobić dość łatwo:

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

.valueMetoda daje rzeczywistą wartość, można określić, które mają być zwracane jako VARCHAR (), które można następnie skontaktować się z oświadczeniem podobne.

Pamiętaj, że to nie będzie strasznie szybkie. Więc jeśli masz pewne pola w swoim kodzie XML, które musisz często sprawdzać, możesz:

utwórz zapisaną funkcję, która pobiera XML i zwraca wartość, której szukasz jako VARCHAR ()
zdefiniuj nowe pole obliczeniowe w tabeli, które wywołuje tę funkcję, i uczyń je kolumną PERSISTED

Dzięki temu w zasadzie „wyodrębniłbyś” pewną część XML do pola obliczeniowego, sprawił, że był on utrwalony, a następnie mógłbyś bardzo efektywnie w nim wyszukiwać (do diabła: możesz nawet zindeksować to pole!).

Marc

— marc_s
źródło

1

Zasadniczo implementuję funkcję wyszukiwania, więc chcę przeszukać kolumnę XML tylko w węzłach „Tekst”, a następnie zwrócić podciąg, aby wskazać, że wyszukiwanie znalazło dopasowanie. Na przykład wyszukując `` cześć tam '' zamiast zwracać całą kolumnę xml, po prostu zwróciłbym podłańcuch, taki jak `` facet powiedział tam cześć i wykonał ... ''

— Jon

1

Pokonaj mnie o 5 sekund. Inną możliwością jest rozważenie skorzystania z wyszukiwania dowolnego tekstu, jeśli twoje dane są dostępne ...

— RickNZ,

10

aby przeszukać całe pole: WHERE xmlField.value ('.', 'varchar (max)') LIKE '% FOO%'

— jhilden

uważaj na brzydkie przestrzenie nazw Xml, jeśli odzyskasz NULL

— Simon_Weaver

87

Jeszcze inną opcją jest rzutowanie XML na nvarchar, a następnie wyszukanie podanego ciągu tak, jakby XML był polem nvarchar.

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

Uwielbiam to rozwiązanie, ponieważ jest czyste, łatwe do zapamiętania, trudne do zepsucia i może być używane jako część klauzuli gdzie.

EDYCJA: Jak wspomina Cliff, możesz użyć:

... nvarchar, jeśli istnieją znaki, które nie są konwertowane na varchar

— Squazz
źródło

3

Tak samo lub nvarchar, jeśli istnieją znaki, które nie są konwertowane na varchar SELECT * FROM Table WHERE CAST (Kolumna jako nvarchar (max)) LIKE '% TEST%'

— Cliff Coulter

[Err] 42000 - [SQL Server] Niemożliwa konwersja jednego lub więcej znaków z XML na docelowe sortowanie

— digz6666

[Err] 22018 - [SQL Server] Jawna konwersja z typu danych xml na tekst jest niedozwolona.

— digz6666

Wygląda na to, że robisz coś nie tak @ digz6666

— Squazz

1

@Squazz Wczoraj ostatni raz głosowałeś na tę odpowiedź. Twój głos jest teraz zablokowany, chyba że ta odpowiedź zostanie zmieniona. :)

— digz6666

10

Inną opcją jest przeszukanie XML jako ciąg znaków, konwertując go na łańcuch, a następnie używając LIKE. Jednak ponieważ kolumna obliczeniowa nie może być częścią klauzuli WHERE, musisz ją opakować w inny element SELECT w następujący sposób:

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

— Carl Onager
źródło

Pamiętaj, że może to ominąć wszelkie selektywne indeksy xml, które możesz mieć, i wpłynąć na wydajność.

— Rudy Hinojosa

0

Oto, czego zamierzam użyć na podstawie odpowiedzi 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

Zwraca podciąg w wyszukiwaniu, w którym istnieją kryteria wyszukiwania

— Jon
źródło

Czy potrzebuję parametrów, aby jakoś zapobiec wtryskowi?

— Jon

2

UWAŻAJ: w tych funkcjach XML rozróżniana jest wielkość liter - DATA.VALUE nie będzie działać! Musi być .value (...)

— marc_s