Câu hỏi Sử dụng câu lệnh LIKE trên Kiểu dữ liệu XML của SQL Server


Nếu bạn có một trường varchar bạn có thể dễ dàng làm SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%' để xem liệu cột đó có chứa một chuỗi nhất định hay không.

Làm thế nào để bạn làm điều đó cho loại XML?

Tôi có sau đây chỉ trả về các hàng có nút 'Văn bản' nhưng tôi cần phải tìm kiếm trong nút đó

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

58
2017-12-02 13:40


gốc


có thể trùng lặp Cách tốt nhất để tìm kiếm dữ liệu được lưu trữ dưới dạng XML trong Sql Server? - Carl Onager


Các câu trả lời:


Bạn sẽ có thể thực hiện điều này khá dễ dàng:

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

Các .value phương thức này cung cấp cho bạn giá trị thực, và bạn có thể định nghĩa rằng nó được trả về như một VARCHAR (), sau đó bạn có thể kiểm tra bằng một câu lệnh LIKE.

Tâm trí bạn, điều này sẽ không quá nhanh. Vì vậy, nếu bạn có một số trường trong XML mà bạn cần kiểm tra rất nhiều, bạn có thể:

  • tạo một hàm được lưu trữ lấy XML và trả về giá trị mà bạn đang tìm kiếm như một VARCHAR ()
  • xác định một trường được tính toán mới trên bảng của bạn gọi hàm này và đặt nó thành cột CÓ THỂ LÊN

Với điều này, về cơ bản bạn sẽ "trích xuất" một phần nhất định của XML vào một trường được tính toán, làm cho nó tồn tại, và sau đó bạn có thể tìm kiếm rất hiệu quả trên nó (heck: bạn thậm chí có thể INDEX trường đó!).

Marc


56
2017-12-02 13:45



Về cơ bản tôi đang triển khai tính năng tìm kiếm vì vậy tôi muốn tìm kiếm cột XML chỉ trên các nút 'Văn bản' và sau đó trả lại chuỗi con để cho biết tìm kiếm đã tìm thấy kết quả phù hợp. Ví dụ tìm kiếm trên 'hi there' thay vì trả lại toàn bộ cột xml, tôi chỉ trả về một chuỗi con như 'chap nói hi there và carry ...' - Jon
Đánh tôi với 5 giây. Một khả năng khác là xem xét việc sử dụng tìm kiếm văn bản miễn phí, nếu dữ liệu của bạn được tuân theo ... - RickNZ
để tìm kiếm toàn bộ được lưu: WHERE xmlField.value ('.', 'varchar (max)') LIKE '% FOO%' - jhilden
xem ra cho pesky Xml không gian tên nếu bạn nhận được NULL trở lại - Simon_Weaver


Tuy nhiên, một tùy chọn khác là để đúc XML như nvarchar, và sau đó tìm kiếm chuỗi đã cho như thể XML vas là một trường nvarchar.

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

Tôi thích giải pháp này vì nó sạch sẽ, dễ nhớ, khó làm rối tung và có thể được sử dụng như một phần của mệnh đề where.

EDIT: Như Cliff đề cập đến nó, bạn có thể sử dụng:

... nvarchar nếu có các ký tự không chuyển đổi thành VARCHAR


32
2018-04-14 14:08



Ditto trên đó, hoặc nvarchar nếu có các ký tự không chuyển đổi thành varchar SELECT * FROM Table WHERE CAST (Cột như nvarchar (max)) LIKE '% TEST%' - Cliff Coulter
[Err] 42000 - [SQL Server] Chuyển đổi một hoặc nhiều ký tự từ XML thành mục tiêu không thể đối chiếu - digz6666
[Err] 22018 - [SQL Server] Không được phép chuyển đổi rõ ràng từ kiểu dữ liệu xml thành văn bản. - digz6666
@ Squazz cảm ơn, nvarchar hoạt động tốt. - digz6666
@Squazz Bạn cuối cùng đã bỏ phiếu cho câu trả lời này ngày hôm qua. Phiếu bầu của bạn hiện bị khóa trừ khi câu trả lời này được chỉnh sửa. :) - digz6666


Một tùy chọn khác là tìm kiếm XML dưới dạng chuỗi bằng cách chuyển đổi nó thành chuỗi và sau đó sử dụng LIKE. Tuy nhiên, như một cột được tính toán không thể là một phần của mệnh đề WHERE, bạn cần bọc nó trong một SELECT khác như sau:

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

10
2018-06-04 08:29





Đây là những gì tôi sẽ sử dụng dựa trên câu trả lời 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

Trả lại chuỗi con trên tìm kiếm nơi tồn tại tiêu chí tìm kiếm


0
2017-12-02 14:40



Tôi có cần các thông số để ngăn ngừa tiêm không? - Jon
ĐẢM BẢO: các hàm XML đó phân biệt chữ hoa chữ thường - DATA.VALUE sẽ không công việc ! Nó cần phải .giá trị(...) - marc_s
Chỉ cần phát hiện ra rằng - Jon
OK, chỉ để đảm bảo bạn biết tại sao nó có thể không hoạt động :-) - marc_s