Вопрос по sql-server, xquery, sql, xml – Как я могу запросить значение в столбце SQL Server XML

111

У меня есть следующий XML хранится в столбце XML (называетсяRoles) в базе данных SQL Server.

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

Я хотел бы перечислить все строки, которые играют в них определенную роль. Эта роль передается по параметру.

Ваш Ответ

7   ответов
7

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

таблице 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'
Что если XML не содержит определения пространства имен?
1

анством имен)

например

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

например XML в параметрах:

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

например Запрос:

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

where col like '%Beta%' ты можешь использовать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
168
select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

Запрос полей XML с использованием t-sql

Сглаживание данных XML в SQL Server

EDIT

Поиграв с этим немного больше, я закончил с этим удивительным запросом, который используетПРИМЕНЕНИЕ КРЕСТА, Этот будет искать в каждой строке (роли) значение, которое вы указали в своем подобном выражении ...

Учитывая эту структуру таблицы:

create table MyTable (Roles XML)

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

Мы можем запросить это так:

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

Вы можете проверить SQL Fiddle здесь:http://sqlfiddle.com/#!3/ae0d5/13

Отличный ответ, я голосую за это, но я думаю, что строка должна быть varchar
@ Бистро, спрашивая о[1] был действительно хороший вопрос. Это означает, что вы выбираете первое значение роли из XML, и это означает, что это будет работать только для поискаAlpha в вашем образце XML. Он не найдет строку, если вы ищетеBeta.
Это отвечает на все мои вопросы, что делает[1] сделать в своем ответе? Bistro
Если у XML есть пространство имен, как мы можем запросить его?
В моем случае мне пришлось запрашивать узлы с определенным значением атрибута. Этот ответ привел к моему решению. Я просто должен был поставить двойные кавычки вокруг значения атрибута.
11

а имя таблицы - table1, вы можете использовать следующее для поиска

DECLARE @Role varchar(50);
SELECT * FROM table1
WHERE Roles.exist ('/root/role = sql:variable("@Role")') = 1
Вы пробовали это? Он находит все, независимо от того, что вы положили в@Role.
использование.value('(/root/role)[1]', 'varchar(max)') like '%yourtext%' вместоexists как объяснил лениэль
это хорошо, здесь любой способ поиска с помощьюlike? forexample /root/role like .... Bistro
5

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

Очевидно, это что-то вроде хака, и я бы не рекомендовал его для каких-либо формальных решений. Однако я считаю эту технику очень полезной при выполнении специальных запросов к столбцам XML в SQL Server Management Studio для SQL Server 2012.

Похожие вопросы