IN and NOT IN for XML column

I have a table with a xml column.
Xml is similar to

  
    abc
    1
  
  
    vf
    2
  
  
    ert
    3
  
  3

Now below query return sl_no column and myxmlcolumn of rows containing xml column having values 'abc' or 'xyz' in node 'user'().Below query i am using similar to IN option of sql.

SELECT
    [mytable].[Sl_no],
    [mytable].[myxmlcolumn]
    FROM [mydb].dbo.[mytable]
    WHERE
        [myxmlcolumn].exist('for $x in /Root/Row where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) return $x') > 0

I want similar kind of query which does same work as sql 'NOT IN' does. That is in my case i want rows not having values 'abc' or 'xyz' in node 'user'() in xml column.
So please help me on this.

The exist() Method (xml Data Type) returns a bit.

1 if at least one node is found and 0 if no nodes are found (empty result set).

To get the rows where neither ABC or XYZ exist you just have to compare the result of exist with 0.

[myxmlcolumn].exist('for $x in /Root/Row 
                     where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) 
                     return $x') = 0

Your FLWOR query can be rewritten using a predicate on the user node instead,

select Sl_no,
       myxmlcolumn
from mytable
where myxmlcolumn.exist('/Root/Row/user[fn:upper-case(text()[1]) = ("ABC", "XYZ")]') = 0

And for the IN version of the query you check if exist returns 1 instead.

select Sl_no,
       myxmlcolumn
from mytable
where myxmlcolumn.exist('/Root/Row/user[fn:upper-case(text()[1]) = ("ABC", "XYZ")]') = 1

StackExchange Database Administrators Q#46315, answer score: 12