SQL Server: exist() and query()XML methods

DBA/Developer can use the exist() XML method in the WHERE clause of a SELECT statement. The exist() method accepts a string representing a specific node, and returns a Boolean value indicating if the node exists in the XML. The exist() method returns a value of 1 if the specified node exists. In this scenario, DBA/Developer passed it a string to search for the <SKU> element with an ID attribute value of "XS-7". DBA/Developer also included CompletionDate ISNULL in the WHERE clause. Therefore, the query will return the ProjectID, Description, and TargetDate for all projects that do not have a completion date and have a material list that contains the XS-7 item. DBA/Developer should not use the following query:

SELECT ProjectID, Description, TargetDate

FROM dbo.Project
WHERE MaterialList.exist
('/Materials/Item/SKU[@ID="XS-7"]')
AND CompletionDate IS NULL;

This query will return an error because the exist() method returns a value of 0 or 1.
This query will generate the following error:

Msg 4145, Level 15, State 1, Line 7

An expression of non-boolean type specified in a context where a condition is expected, near

'AND'.DBA/Developer should not use the following query:

SELECT *, MaterialList.query ('Materials/Item/SKU[@ID="XS-7"]')
FROM dbo.Project;

This query will return a row for all projects because no WHERE clause was specified. For products with a material list including the XS-7 item, it would return a single <SKU> element as follows:

<SKU ID="XS-7" ItemDesc="Wall Brackets" />

DBA/Developer should not use the following query:

SELECT ProjectID, Description, TargetDate

FROM dbo.Project
WHERE MaterialList.query
('/Materials/Item/SKU[.="XS-7"]')
AND CompletionDate IS NULL;

The query() method is used to query and retrieve XML elements and attributes from an XML instance. The method accepts a string XQuery expression that determines which elements and element attributes are extracted. It will return untyped XML, not a Boolean value that can be referenced in a WHERE clause. This query will return the following error:

Msg 4145, Level 15, State 1, Line 5