Sql-server – IN and NOT IN for XML column

sql serversql-server-2008-r2xmlxquery

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

<Root>
  <Row>
    <user>abc</user>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <user>vf</user>
    <Rowid>2</Rowid>
  </Row>
  <Row>
    <user>ert</user>
    <Rowid>3</Rowid>
  </Row>
  <Maxrowid>3</Maxrowid>
</Root>

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.

Best Answer

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
Related Question