Return xml sequences where an attribute doesn't contain a specific character Consider the following simple XML:

&lt;xml&gt;
  &lt;customer name="Max"&gt;
    &lt;email address="me@you.com" /&gt;
  &lt;/customer&gt;
  &lt;customer name="Erik"&gt;
    &lt;email address="erik@your-mom.com" /&gt;
  &lt;/customer&gt;
  &lt;customer name="Brent"&gt;
    &lt;email address="brentcom" /&gt;
  &lt;/customer&gt;
&lt;/xml&gt;


I want to get a list of &lt;Customer&gt; sequences where the address attribute of the &lt;email&gt; item does not contain an @.

So, I want output that looks like:

&lt;customer name="Brent"&gt;
  &lt;email address="brentcom" /&gt;
&lt;/customer&gt;


mcve:

DECLARE @x XML = '&lt;xml&gt;
&lt;customer name="Max"&gt;&lt;email address="me@you.com" /&gt;&lt;/customer&gt;
&lt;customer name="Erik"&gt;&lt;email address="erik@your-mom.com" /&gt;&lt;/customer&gt;
&lt;customer name="Brent"&gt;&lt;email address="brentcom" /&gt;&lt;/customer&gt;
&lt;/xml&gt;';


This query:

SELECT WithValidEmail = @x.query('/xml/customer/email[contains(@address, "@")]')
    , WithInvalidEmail = @x.query('/xml/customer/email[contains(@address, "@")] = False');


Returns:

╔═══════════════════════════════════════╦══════════════════╗
║            WithValidEmail             ║ WithInvalidEmail ║
╠═══════════════════════════════════════╬══════════════════╣
║ &lt;email address="me@you.com" /&gt;        ║                  ║
║ &lt;email address="erik@your-mom.com" /&gt; ║ false            ║
╚═══════════════════════════════════════╩══════════════════╝


This query:

SELECT WithInValidEmail = @x.query('/xml/customer/email')
WHERE @x.exist('/xml/customer/email[contains(@address, "@")]') = 0;


Returns:

╔══════════════════╗
║ WithInValidEmail ║
╚══════════════════╝
    (no results)


The WHERE clause in the query above is eliminating the entire set of XML because at least a single sequence exists where the email address contains an "@" sign.
An easy way to do this is to use the nodes method to get right to the address attribute and check for your @ sign.

The problem with the way you're looking now is that it's only checking that any email address has an @ in it. Parsing the XML nodes out lets you check individual emails for it.

DECLARE @x XML
    = '&lt;xml&gt;
&lt;customer name="Max"&gt;&lt;email address="me@you.com" /&gt;&lt;/customer&gt;
&lt;customer name="Erik"&gt;&lt;email address="erik@your-mom.com" /&gt;&lt;/customer&gt;
&lt;customer name="Brent"&gt;&lt;email address="brentcom" /&gt;&lt;/customer&gt;
&lt;/xml&gt;';


SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM   @x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;


If you need to query an actual table with an XML column like this, you'd just CROSS APPLY the nodes method like thusly:

SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM @x_table AS xt
CROSS APPLY xt.x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;


If you want to bring all the &lt;customer&gt;...&lt;/customer&gt; XML for that "row" back, you can walk the axis back. Just be aware that walking back can make performance a bit woogy for large XML blocks.

SELECT x.c.query('..')
FROM @x_table AS xt
CROSS APPLY xt.x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;


Another way of doing it is:

SELECT @x.query('/xml/customer[email/@address[not(contains(., "@"))]]') answer


Moving the square brackets to wrap around the email node effectively make that the WHERE clause applied to the customer node.  Translating this XQuery to English looks like:


  Get me all xml/customer nodes with an email node which has an address attribute
  which does not contain the @ symbol