Comment interroger une valeur dans une colonne SQL Server XML

Question

J'ai XML suivant stocké dans une colonne XML (appelée Roles ) dans une base de données SQL Server.

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

Je voudrais énumérer toutes les lignes qui ont un rôle spécifique en elles. Ce rôle passé par paramètre.

Answer 1

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

Ces pages vont vous montrer plus sur la façon de requête XML en T-SQL:

L'interrogation des champs XML à l'aide de t-sql

L'aplatissement de Données XML dans SQL Server

MODIFIER

Après avoir joué avec elle un peu plus, je me suis retrouvé avec cette incroyable requête qui utilise la CROIX APPLIQUER. Celui-ci va rechercher toutes les lignes (rôle) pour la valeur que vous mettez dans votre expression...

Compte tenu de cette structure de la table:

create table MyTable (Roles XML)

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

Nous pouvons requête comme ceci:

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

Vous pouvez vérifier le SQL Violon ici: http://sqlfiddle.com/#!3/ae0d5/13

Answer 2

 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
 

Si vous voulez que la requête fonctionne en tant que where col like '%Beta%' vous pouvez utiliser contains

 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
 

Answer 3

Si votre nom de champ est Roles et que le nom de table est Table1, vous pouvez utiliser ce qui suit pour rechercher

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

Answer 4

J'ai utilisé l'instruction ci-dessous pour récupérer les valeurs dans le XML dans la table 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'