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

rajesh yadav 171 Reputation points
2020-12-24T12:52:41.83+00:00
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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,583 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 111.5K Reputation points
    2020-12-24T13:01:18.207+00:00

    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)
    
    0 comments No comments

  2. Yitzhak Khabinsky 24,836 Reputation points
    2020-12-24T13:31:51.137+00:00

    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     |
    +----+------------+--------+
    

  3. EchoLiu-MSFT 14,571 Reputation points
    2020-12-25T09:43:13.467+00:00

    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

    0 comments No comments