CODEWITHSUNDEEP
sql-serverxmlxquery
ca9163d9
ca9163d9

Reputation: 29159

Check if XML is just a particular string?

The following SQL returns 1 for all the rows of the second and the third columns. However, shouldn't it just return 1s for the first row? 

with    t as ( select   cast('SomeText' as xml) x
               union all
               select   cast('<s>Test</s>' as xml)
             )
    select  * ,
            x.exist('contains(/text()[1], "SomeText.")') ,
            x.exist('/text() = "SomeText."')
    from    t
    where   x.exist('contains(/text()[1], "SomeText.")') = 1 
            and x.exist('/text() = "SomeText."') = 1;

I know using x.value('/', 'nvarchar(max)') = 'SomeText.' can resolve the issue.

The question is about why the XQuery x.exist('/text() = "SomeText."') or x.exist('contains(/text()[1], "SomeText.")') are not working (return true always).

Upvotes: 1

Views: 62

Answers (1)

Martin Smith
Martin Smith

Reputation: 452957

Try

with    t as (  select   cast('SomeText' as xml) x
               union all
               select   cast('SomeText.' as xml) x
               union all
               select   cast('<s>Test</s>' as xml)
             )
    select  * ,
            x.query('contains(/text()[1], "SomeText.")') ,
            x.query('/text() = "SomeText."'),
            x.exist('.[contains(/text()[1], "SomeText.")]') ,
            x.exist('.[text()[1] eq "SomeText."]')
    from    t

Which returns

+-------------+------------------+------------------+------------------+------------------+
|      x      | (No column name) | (No column name) | (No column name) | (No column name) |
+-------------+------------------+------------------+------------------+------------------+
| SomeText    | false            | false            |                0 |                0 |
| SomeText.   | true             | true             |                1 |                1 |
| <s>Test</s> | false            | false            |                0 |                0 |
+-------------+------------------+------------------+------------------+------------------+

I imagine your original code is returning these rows because the result of false does still exist.

declare @x xml;  
set @x='';  
select @x.exist('false()');

Does still return 1

Upvotes: 2

Related Questions