For this you want to use the .exist() XML function as it will return a BIT (i.e. boolean) value indicating whether or not the XQuery find anything.
To handle the non-static location of an element, you would use either * (indicating that it should check all nodes of a particular level, but not other levels), or // (indicating that it should check all nodes at that level and below).
The following examples use the example query from the question as a base, and adds a few test cases to place the element at different levels, and adds a test case that changes the name to show that the XQuery isn't just selecting everything.
Test Setup (run once)
SET NOCOUNT ON;
CREATE TABLE #Table (ID INT NOT NULL, XmlCol XML);
INSERT INTO #Table (ID, XmlCol) VALUES (1, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
<MCTLocation>
<Address>1234 Main Street</Address>
<ContactFName>Chris</ContactFName>
<ContactLName>Brandt</ContactLName>
</MCTLocation>
</MCTLocations>
</root>');
INSERT INTO #Table (ID, XmlCol) VALUES (2, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
<MCTLocation>
<Address>1234 Main Street</Address>
<ContactFName>Chris</ContactFName>
<ContactLName>Grandt</ContactLName>
</MCTLocation>
</MCTLocations>
</root>');
INSERT INTO #Table (ID, XmlCol) VALUES (3, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
<MCTLocation>
<Address>1234 Main Street</Address>
<ContactFName>Chris</ContactFName>
</MCTLocation>
</MCTLocations>
<ContactLName>Brandt</ContactLName>
</root>');
INSERT INTO #Table (ID, XmlCol) VALUES (4, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
<MCTLocation>
<Address>1234 Main Street</Address>
<ContactFName>Chris</ContactFName>
</MCTLocation>
<NewElement>
<SubElement>
<ContactLName>Brandt</ContactLName>
</SubElement>
</NewElement>
</MCTLocations>
</root>');
INSERT INTO #Table (ID, XmlCol) VALUES (5, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
<MCTLocation>
<Address>1234 Main Street</Address>
<ContactFName>Chris</ContactFName>
</MCTLocation>
<NewerElement>
<ContactLName>Brandt</ContactLName>
</NewerElement>
</MCTLocations>
</root>');
INSERT INTO #Table (ID, XmlCol) VALUES (6, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
<MCTLocation>
<Address>1234 Main Street</Address>
<ContactFName>Chris</ContactFName>
</MCTLocation>
<NewerElement>
</NewerElement>
</MCTLocations>
</root>
<ContactLName>Brandt</ContactLName>
');
Test 1 (* in place of a node name)
This will check all nodes at the specified level, which in this case is just under <root>. But it won't check other levels.
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'/*/ContactLName[text()="Brandt"]') = 1;
Returns row with ID value of 3.
Test 2 (* in place of a node name)
This will check all nodes at the specified level, which in this case is just under <root><MCTLocations>. But it won't check other levels.
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'/root/MCTLocations/*/ContactLName[text()="Brandt"]') = 1;
Returns rows with ID values of 1 and 5.
Test 3 (// in place of a node name)
This will check all nodes starting at the specified level, which in this case is just under <root><MCTLocations>, and below.
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'/root/MCTLocations//ContactLName[text()="Brandt"]') = 1;
Returns rows with ID values of 1, 4, and 5.
Test 4 (/* or */ in place of a node name)
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'*//ContactLName[text()="Brandt"]') = 1;
-- and:
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'//*/ContactLName[text()="Brandt"]') = 1;
Both return rows with ID values of 1, 3, 4, and 5.
These do not return row ID of 6 due to the * being a placeholder for a single node, hence the highest level allowed would be under <root> (or any top-level node).
Test 5 (// at the top level)
This will check all nodes starting at the top level.
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'//ContactLName[text()="Brandt"]') = 1;
Returns rows with ID values of 1, 3, 4, 5, and 6.
Test 6 (use local variable value for element text in XQuery)
DECLARE @Name NVARCHAR(50) = N'Brandt';
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;
SET @Name = N'Grandt';
-- exact same query, just different value in the variable
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;
First query returns rows with ID values of 1, 3, 4, 5, and 6.
Second query returns row with ID value of 2.
Test 7 (use function and string literal for element name in XQuery)
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'//.[local-name()="NewerElement"]') = 1;
Returns rows with ID values of 5 and 6.
Test 8 (use function with local variable value for element name in XQuery)
DECLARE @Node NVARCHAR(50) = N'SubElement';
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@Node")]') = 1;
Returns row with ID value of 4.
Test 9 (put all of the pieces together)
DECLARE @NodeName NVARCHAR(50) = N'ContactLName',
@NodeText NVARCHAR(500) = N'Brandt';
SELECT *
FROM #Table tmp
WHERE tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@NodeName")]
[text()=sql:variable("@NodeText")]') = 1;
Returns rows with ID values of 1, 3, 4, 5, and 6.
General XML Note:
XML data (in SQL Server) is encoded as UTF-16 Little Endian, same as NVARCHAR / NCHAR. Hence it is best to prefix sting literals with a capital-N when the value is really XML.
