Làm cách nào tôi có thể truy vấn một giá trị trong cột XML của SQL Server?

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

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

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%'

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.

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

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'