112

Tôi đã theo dõi XML được lưu trữ trong một cột XML (được gọi Roles) trong cơ sở dữ liệu SQL Server.

<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>

Tôi muốn liệt kê tất cả các hàng có vai trò cụ thể trong đó. Vai trò này được thông qua bởi tham số.

|
172
select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

Các trang này sẽ cho bạn biết thêm về cách truy vấn XML trong T-SQL:

Truy vấn các trường XML bằng cách sử dụng t-sql

Làm phẳng dữ liệu XML trong SQL Server

CHỈNH SỬA

Sau khi chơi với nó thêm một chút nữa, tôi đã kết thúc với truy vấn tuyệt vời này sử dụng CROSS ỨNG DỤNG . Cái này sẽ tìm kiếm mọi hàng (vai trò) cho giá trị bạn đặt trong biểu thức thích của bạn ...

Cho cấu trúc bảng này:

create table MyTable (Roles XML)

insert into MyTable values
('<root>
   <role>Alpha</role>
   <role>Gamma</role>
   <role>Beta</role>
</root>')

Chúng ta có thể truy vấn nó như thế này:

select * from 

(select 
       pref.value('(text())[1]', 'varchar(32)') as RoleName
from 
       MyTable CROSS APPLY

       Roles.nodes('/root/role') AS Roles(pref)
)  as Result

where RoleName like '%ga%'

Bạn có thể kiểm tra SQL Fiddle tại đây: http://sqlfiddle.com/#!3/ae0d5/13

|
  • 1

    Nó trả lời tất cả câu hỏi của tôi, [1]làm gì trong câu trả lời của bạn?

    – Hoàng Thế Minh 04:12:46 27/04/2012
  • 1

    Câu trả lời tuyệt vời, tôi bỏ phiếu cho câu hỏi này, nhưng chuỗi nên là varchar tôi đoán

    – Lý Diệu Thúy 04:13:09 27/04/2012
  • 1

    @Bistro Hỏi về [1]là một câu hỏi thực sự tốt. Điều đó có nghĩa là bạn chọn giá trị vai trò đầu tiên từ XML và điều đó có nghĩa là điều này sẽ chỉ hoạt động để tìm Alphatrong xml mẫu của bạn. Nó sẽ không tìm thấy hàng nếu bạn tìm kiếm Beta.

    – Hồ Hoàng Duy 05:37:49 27/04/2012
  • 1

    Trong trường hợp của tôi, tôi đã phải truy vấn các nút có giá trị thuộc tính cụ thể. Câu trả lời này đã dẫn đến giải pháp của tôi. Tôi chỉ phải đặt dấu ngoặc kép xung quanh giá trị thuộc tính.

    – Lê Việt Yến 18:31:07 20/01/2014
  • 1

    Nếu XML có một không gian tên, làm thế nào để chúng ta truy vấn nó?

    – Bùi Hà Khuê 22:35:10 10/05/2018
34
declare @T table(Roles xml)

insert into @T values
('<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>')

declare @Role varchar(10)

set @Role = 'Beta'

select Roles
from @T
where Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1

Nếu bạn muốn truy vấn hoạt động như where col like '%Beta%'bạn có thể sử dụngcontains

declare @T table(Roles xml)

insert into @T values
('<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>')

declare @Role varchar(10)

set @Role = 'et'

select Roles
from @T
where Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1
|
11

nếu tên trường của bạn là Vai trò và tên bảng là bảng1, bạn có thể sử dụng theo sau để tìm kiếm

DECLARE @Role varchar(50);
SELECT * FROM table1
WHERE Roles.exist ('/root/role = sql:variable("@Role")') = 1
|
7

Tôi đã nghĩ ra một công việc đơn giản xung quanh bên dưới cũng dễ nhớ :-)

select * from  
(select cast (xmlCol as varchar(max)) texty
 from myTable (NOLOCK) 
) a 
where texty like '%MySearchText%'
|
5

Bạn có thể làm như sau

declare @role varchar(100) = 'Alpha'
select * from xmltable where convert(varchar(max),xmlfield) like '%<role>'+@role+'</role>%'

Rõ ràng đây là một chút hack và tôi sẽ không đề xuất nó cho bất kỳ giải pháp chính thức nào. Tuy nhiên, tôi thấy kỹ thuật này rất hữu ích khi thực hiện các truy vấn adhoc trên các cột XML trong SQL Server Management Studio cho SQL Server 2012.

|
1

Lời khuyên hữu ích. Truy vấn một giá trị trong cột XML của SQL Server (XML có không gian tên)

ví dụ

Table [dbo].[Log_XML] contains columns Parametrs (xml),TimeEdit (datetime)

ví dụ: XML trong Parametrs:

<ns0:Record xmlns:ns0="http://Integration"> 
<MATERIAL>10</MATERIAL> 
<BATCH>A1</BATCH> 
</ns0:Record>

ví dụ: Truy vấn:

select
 Parametrs,TimeEdit
from
 [dbo].[Log_XML]
where
 Parametrs.value('(//*:Record/BATCH)[1]', 'varchar(max)') like '%A1%'
 ORDER BY TimeEdit DESC
|
1

Tôi đã sử dụng câu lệnh dưới đây để lấy các giá trị trong XML trong bảng Sql

with xmlnamespaces(default 'http://test.com/2008/06/23/HL.OnlineContract.ValueObjects')
select * from (
select
            OnlineContractID,
            DistributorID,
            SponsorID,
    [RequestXML].value(N'/OnlineContractDS[1]/Properties[1]/Name[1]', 'nvarchar(30)') as [Name]
   ,[RequestXML].value(N'/OnlineContractDS[1]/Properties[1]/Value[1]', 'nvarchar(30)') as [Value]
     ,[RequestXML].value(N'/OnlineContractDS[1]/Locale[1]', 'nvarchar(30)') as [Locale]
from [OnlineContract]) as olc
where olc.Name like '%EMAIL%' and olc.Value like '%EMAIL%' and olc.Locale='UK EN'
|

Câu trả lời của bạn (> 20 ký tự)

Bằng cách click "Đăng trả lời", bạn đồng ý với Điều khoản dịch vụ, Chính sách bảo mật and Chính sách cookie của chúng tôi.

Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ hoặc hỏi câu hỏi của bạn.