sql-server - 如何在 SQL Server 中查询 XML 列?
问题描述
我有一个名为“Table1”的表,表中有名为“Name”和“XMLDefinition”的列。
Name XMLDefinition
--------------------------
Name1 xmlLink1
Name2 xmlLink2
在每个 XML 中,示例如下所示:
<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<QueryView>
<QueryKey />
</QueryView>
<Description>
</Description>
<QueryFields>
<f />
<f />
</QueryFields>
<FilterFields>
<f ObjectName="TABLE5" ColumnName="ID">
<DateFilterTypes />
<FuzzyDateFilterTypes />
<MonthDayFilterTypes />
<Values>
<v>0</v>
</Values>
<TranslatedValues>
<v>No</v>
</TranslatedValues>
<DataType>Boolean</DataType>
</f>
<f ObjectName="TABLE2" ColumnName="USERID">
<DateFilterTypes />
<FuzzyDateFilterTypes />
<MonthDayFilterTypes />
<Values>
<v>B80055</v>
</Values>
<TranslatedValues>
<v>B80055</v>
</TranslatedValues>
<DataType>String</DataType>
</f>
</FilterFields>
</Query>
只要在 XML 内容中包含 ObjectName = "TABLE2" AND ColumnName = "USERID",我想从 TABLE1 返回名称。
我尝试了以下方法,虽然它没有出错,但它返回 0 条记录:
SELECT
a.Name,
X.Y.value('(f)[1]', 'VARCHAR(MAX)') as Object
FROM TABLE1 a
OUTER APPLY a.XMLDefinition.nodes('Query/FilterFields/f') as X(Y)
WHERE X.Y.value('(ObjectName)[1]', 'VARCHAR(MAX)') = 'TABLE2'
AND X.Y.value('(ColumnName)[1]', 'VARCHAR(MAX)') = 'USERID'
我不确定我错过了什么,因为我似乎正在从 Query > FilterFields > f 向下钻取,我假设我可以根据此处的 ObjectName 和 ColumnName 进行过滤。
尝试 2 更新:
SELECT Name from TABLE1
WHERE XMLDefinition.value('(/Query/QueryView/Description/QueryFields/FilterFields/f/@ObjectName) [1] ',' varchar(max)') = 'TABLE2'
AND XMLDefinition.value('(/Query/QueryView/Description/QueryFields/FilterFields/f/@ColumnName) [1] ',' varchar(max)') = 'USERID'
通过钻取每个标签尝试此尝试后,它仍然给我 0 个结果。
尝试 3 更新:
select
a.Name,
X.Y.query(N'.') as [Object] --this returns the XML of the <f> element
from dbo.Table1 a
cross apply a.XMLDefinition.nodes('//*:f[@ObjectName="TABLE2"][@ColumnName="USERID"][1]') as X(Y);
我不知道为什么,但我尝试了这个,现在它工作并返回了我正在寻找的结果。我是 XML 的新手,但我认为这很有效,因为它忽略了 f 标记之前的所有命名空间和先前的标记?
解决方案
下面的代码可能会满足您的需求。请注意,cross apply
它将仅返回与 XPath 查询匹配的 dbo.Table 行,而不是outer apply
返回所有 dbo.Table 行,但仅返回与 XPath 查询匹配的那些行的 XML 派生值:
create table dbo.Table1 (
Name nvarchar(10),
XMLDefinition xml
);
insert dbo.Table1 (Name, XMLDefinition) values
(N'Name1', N'<xmlLink1 />'),
(N'Name2', N'<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<FilterFields>
<f ObjectName="TABLE2" ColumnName="USERID" ParentPath="TABLE2" DisplayPath="TABLE2" CompareType="Or" UseLeftParenthesis="true" LeftParenthesisCount="1" IncludeCurrentNode="true">
<DateFilterTypes />
<FuzzyDateFilterTypes />
<MonthDayFilterTypes />
<Values>
<v>B80055</v>
</Values>
<TranslatedValues>
<v>B80055</v>
</TranslatedValues>
<DataType>String</DataType>
</f>
</FilterFields>
</Query>');
select
a.Name,
X.Y.query(N'.') as [Object] --this returns the XML of the <f> element
from dbo.Table1 a
cross apply a.XMLDefinition.nodes(N'/Query/FilterFields/f[@ObjectName="TABLE2"][@ColumnName="USERID"][1]') as X(Y);
推荐阅读
- python - 如何将类属性传递给python中的函数并将其用作类的属性
- python-3.x - 如何从类外部的while循环控制类中的while循环迭代
- javascript - set() 权限被 Firebase 拒绝
- python - 在内部转移 (stock.picking) 错误中自动填充初始需求 (stock.move 表) 中的产品
- node.js - 如何将 ejs 转换为车把?
- swift - 如何在 Swift 中摆脱 Objective-C 的 KVO?
- javascript - javascript onclick 更改 mp3 播放器
- numpy - 调整 numpy 图像数组的大小
- python - 如何修复索引 4 超出轴 0 大小为 4 的范围?
- server-side-rendering - ReferenceError:在角度 9 中运行 SSR 时未定义窗口