Come posso interrogare un valore nella colonna XML di SQL Server

Ho seguito XML memorizzato in una colonna XML (chiamato Roles ) in un database SQL Server.

  Alpha Beta Gamma  

Vorrei elencare tutte le righe che hanno un ruolo specifico in esse. Questo ruolo è passato per parametro.

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

Queste pagine ti mostreranno di più su come interrogare XML in T-SQL:

Interrogare i campi XML usando t-sql

Appiattimento dei dati XML in SQL Server

MODIFICARE

Dopo aver giocato un po ‘di più, ho finito con questa incredibile query che usa APPLICAZIONI CROSS . Questo cercherà ogni riga (ruolo) per il valore che metti nella tua espressione simile …

Data questa struttura della tabella:

 create table MyTable (Roles XML) insert into MyTable values (' Alpha Gamma Beta ') 

Possiamo interrogarlo in questo modo:

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

Puoi controllare SQL Fiddle qui: http://sqlfiddle.com/#!3/ae0d5/13

 declare @T table(Roles xml) insert into @T values (' Alpha Beta Gamma ') declare @Role varchar(10) set @Role = 'Beta' select Roles from @T where Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1 

Se vuoi che la query funzioni come where col like '%Beta%' contains where col like '%Beta%' puoi usare

 declare @T table(Roles xml) insert into @T values (' Alpha Beta Gamma ') declare @Role varchar(10) set @Role = 'et' select Roles from @T where Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1 

se il nome del campo è Ruoli e il nome della tabella è tabella1, è ansible utilizzare il seguente modo di cercare

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

Ho trovato un semplice lavoro in giro che è facile da ricordare 🙂

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

Potresti fare quanto segue

 declare @role varchar(100) = 'Alpha' select * from xmltable where convert(varchar(max),xmlfield) like '%'[email protected]+'%' 

Ovviamente questo è un po ‘un trucco e non lo consiglierei per nessuna soluzione formale. Tuttavia trovo questa tecnica molto utile quando si eseguono query ad hoc su colonne XML in SQL Server Management Studio per SQL Server 2012.

Ho usato la seguente istruzione per recuperare i valori nell’XML nella tabella 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'