how to put not in in xml while reading xml in sqlserver stored procedure

Question

i have used to ands in flowing query for @id attribute , i want to use "not in"  ?

DECLARE @Data XML = N'<root>
      <column id="1" isok="true">OK</column>
      <column id="2" isok="false">NOT OK</column>
      <column id="3" isok="   TRUE   ">OK</column>
      <column id="4" isok=" false">NOT OK</column>
  </root>';

  SELECT c.value('@id', 'INT') AS [id]
  , c.value('@isok', 'VARCHAR(10)') AS [isok]
  , c.value('(./text())[1]', 'VARCHAR(10)') AS [column]
  FROM @Data.nodes('/root/column[(fn:lower-case(@isok) cast as xs:token?) = "true" and (fn:lower-case(@id) cast as xs:token?) != "4" and (fn:lower-case(@id) cast as xs:token?) != "5" ]') AS t(c);

yours sincerely

Answer 1

If you want a ‘NOT IN’ SQL operator, then try this query:

SELECT 
    c.value('@id', 'INT') AS [id],
    c.value('@isok', 'VARCHAR(10)') AS [isok],
    c.value('(./text())[1]', 'VARCHAR(10)') AS [column]
FROM @Data.nodes('/root/column[(fn:lower-case(@isok) cast as xs:token?) = "true"]') AS t(c)
where c.value('@id', 'INT') not in (4, 5)

Answer 2

Please try the following.
The XML's id attribute has integer values. That's why there is no need for any manipulation of it.

SQL

DECLARE @Data XML = N'<root>
       <column id="1" isok="true">OK</column>
       <column id="2" isok="false">NOT OK</column>
       <column id="3" isok="   TRUE   ">OK</column>
       <column id="4" isok=" false">NOT OK</column>
   </root>';

SELECT c.value('@id', 'INT') AS [id]
, c.value('@isok', 'VARCHAR(10)') AS [isok]
, c.value('(./text())[1]', 'VARCHAR(10)') AS [column]
FROM @Data.nodes('/root/column[(fn:lower-case(@isok) cast as xs:token?) = "true" 
 and (@id != ("4", "5"))]') AS t(c);

Output

+----+------------+--------+
| id |    isok    | column |
+----+------------+--------+
|  1 | true       | OK     |
|  3 |    TRUE    | OK     |
+----+------------+--------+

Answer 3

Hi @rajesh yadav ,

Experts have provided some solutions. Have your problems been solved?
If you have any question, please feel free to let me know.

Regards
Echo