type - ¿Cómo puedo consultar un valor en la columna XML de SQL Server?

sql server xml data type nodes

Question

He seguido XML almacenado en una columna XML (llamada Roles ) en una base de datos de SQL Server.

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

Me gustaría enumerar todas las filas que tienen un rol específico en ellas. Este rol pasó por el parámetro.

Answer 1

Podrías hacer lo siguiente

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

Obviamente, este es un truco y no lo recomendaría para ninguna solución formal. Sin embargo, considero que esta técnica es muy útil cuando realizo consultas adhoc en columnas XML en SQL Server Management Studio para SQL Server 2012.

Answer 2

Se me ocurrió un trabajo sencillo debajo que también es fácil de recordar :-)

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

Answer 3

Utilicé la declaración a continuación para recuperar los valores en el XML en la tabla 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''

Answer 4

si su nombre de campo es Roles y el nombre de la tabla es table1 puede usar following para buscar

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

Answer 5

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 quieres que la consulta funcione como where col like ''%Beta%'' puedes usar 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 6

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

Estas páginas le mostrarán más sobre cómo consultar XML en T-SQL:

Consultar campos XML usando t-sql

Aplanamiento de datos XML en SQL Server

EDITAR

Después de jugar con él un poco más, terminé con esta sorprendente consulta que usa CROSS APPLY . Éste buscará en cada fila (rol) el valor que ponga en su expresión similar ...

Dada esta estructura de tabla:

create table MyTable (Roles XML)

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

Podemos consultarlo así:

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

Puede consultar el SQL Fiddle aquí: http://sqlfiddle.com/#!3/ae0d5/13