如何查询 SQLServerXML 列中的值

我跟踪存储在 SQLServer 数据库中的 XML 列(称为 Roles)中的 XML。

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

我想列出所有具有特定角色的行。这个角色通过参数传递。

509237 次浏览
小开

如果字段名为 Roles，表名为 table1，则可以使用以下命令进行搜索

DECLARE @Role varchar(50);
SELECT * FROM table1
WHERE Roles.exist ('/root/role = sql:variable("@Role")') = 1
小开
最佳答案 
select
Roles
from
MyTable
where
Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

如果您的列不是 XML，则需要对其进行转换。还可以使用其他语法查询 XML 数据的某些属性。举个例子。

假设数据列具有以下内容:

<Utilities.CodeSystems.CodeSystemCodes iid="107" CodeSystem="2" Code="0001F" CodeTags="-19-"..../>

... 你只需要那些 CodeSystem = 2然后你的查询将是:

select
[data]
from
[dbo].[CodeSystemCodes_data]
  

where
CAST([data] as XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

这些页面将向您展示更多关于如何在 T-SQL 中查询 XML 的内容:

使用 t-sql 查询 XML 字段

SQL Server 中 XML 数据的扁平化

剪辑

在更多地使用它之后，我得到了这个使用 交叉适用的令人惊奇的查询。这个程序将在每一行(角色)中搜索您放在 like 表达式中的值..。

根据这个表格结构:

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/#!18/dc4d2/1/0

小开 
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
小开

我使用下面的语句检索 Sql 表中 XML 中的值

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'
小开

您可以执行以下操作

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

显然，这是一个有点黑客，我不会推荐它为任何正式的解决方案。但是，我发现这种技术在对 SQLServerManagementStudioforSQLServer2012中的 XML 列进行即席查询时非常有用。

小开

我想出了一个简单的工作，在下面这是很容易记住的: -)

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

有用的提示: 查询 SQLServerXML 列中的值(带命名空间的 XML)

例如:。

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
小开

您可以查询整个标记，或者只查询特定的值。

declare @myDoc xml
set @myDoc =
'<Root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://stackoverflow.com">
<Child>my value</Child>
</Root>'


select @myDoc.query('/*:Root/*:Child') -- whole tag
select @myDoc.value('(/*:Root/*:Child)[1]', 'varchar(255)') -- only value
小开

如果您想找到除“ Alpha”之外的其他节点，查询应该是这样的:

select Roles from MyTable where Roles.exist('(/*:root/*:role[contains(.,"Beta")])') = 1


提交答案