SQL Server XML exist()

Well, your XPath expression here is the "culprit":

query('//branch')

This says: select all <branch> nodes from the entire document. It is just doing what you're telling it to do, really....

What's wrong with this query here??

SELECT     XMLData.query('/library/books/book[@type=sql:variable("@genre")]//branch')FROM dbo.TableA

That would retrieve all the <branch> subnodes for the <book> node that has type="SF" as an attribute....

What are you trying to achieve with your query(), exist() and value() all in the same statement?? Quite possibly, it can be done a lot easier....

Also: I think you're misinterpreting what .exist() in SQL Server XQuery does. If you have your statement here:

 SELECT (some columns) FROM dbo.TableA WHERE XMLData.exist('//book[@type = sql:variable("@genre")]') = 1

you're basically telling SQL Server to retrieve all rows from dbo.TableA where the XML stored in XMLData contains a <book type=.....> node - you are selecting rows from the table - NOT applying a selection to the XMLData column's content...

The XML you supplied does not lend itself to an exist statement. If you had multiple XML statements and needed to find the one where it contained some value, then the statement would have been more relevant.

The where clause you supplied just checks if the condition exists and if it does, selects all the branches elements, not just the one where the condition is true.For example, the following (obviously) does not return anything:

SELECT @xmldata.query('//branch') from TableA WHERE @xmldata.exist('//book[./@type = "BLAH"]') = 1

But here is something to show you can use all three in one select statement.

SELECT T.c.query('./title').value('.', 'varchar(250)') as title,        T.c.exist('.[@type eq "SF"]') as IsSF  from @xmldata.nodes('//book') T(c)

It's more efficient with XML to filter using the cross apply operator to filter to the required node then select the query from the returned nodes. To query the child nodes you also need to include the root . in the query so in this case .//branch rather than //branch.

declare @genre varchar(15) = 'SF'select l.query('.//branch') from TableAcross apply XmlData.nodes('library/books/book[@type=sql:variable("@genre")]') n (l)

You can still add the exists clause if you want but this will actually add additional unnecessary overhead

WHERE XMLData.exist('//book[./@type = sql:variable("@genre")]') = 1

Hope this helps.D