Multiple conditions in Exist() clause of XQuery in SQL Server-CodePudding

I use XQuery in SQL Server and my question is: how to use two or more conditions in the Exist() clause?

For example:

[dbo].[A].[XmlList]:

<Property>
  <Value Name="Patt1">Pval1</Value>
  <Value Name="Patt1">Pval999</Value>
  <Value Name="patt2">Pval2</Value>
</Property>

Select query :

SELECT *
FROM A
WHERE XmlList.exist(N'/Property/Value [@Name="Patt1" and value="Pval1"]')

@Name is an attribute, and value is node value to look for

CodePudding user response：

You may use [@Name="Patt1"] expression to check an attribute value and then [text()="Pval1"] expression to check a node value.

SELECT *
FROM A
WHERE XmlList.exist(N'/Property/Value[@Name="Patt1"][text()="Pval1"]')=1

You also may use a nested structure like this:

SELECT *
FROM A
WHERE XmlList.exist(N'/Property[Value[@Name="Patt1"]="Pval1"]')=1

Expression Value[@Name="Patt1"] checks an attribute value. Expression [Value[...]="Pval1"] checks a node value.