如何在SQL Server中查询XML列?

Question

我有一个名为"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>

我想从TABLE1返回Name，只要在XML内容中包含ObjectName = "TABLE2"AND ColumnName = "USERID"

我已经尝试了下面的，虽然它没有错误，它返回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'

我不确定我错过了什么，因为我似乎正在从查询>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标记之前的所有名称空间和先前标记。

Answer 1

下面的代码可能满足您的要求。注意，cross apply将只返回dbo。匹配XPath查询的表行，而不是将返回所有dbo的outer apply。表行，但只针对匹配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);