Using XPath .exist to look for elements within a decimal range

I am trying to do a range comparison in an xpath exist - but it is being ignored (returning all rows). The element "caruwmarketfactor" is a decimal value (stored untyped) - can anybody see what I am doing wrong?

The element can appear anywhere in the document - I know in all our cases it will be in there only 1 time. I am looking for the range of .70 to .75.

declare @table table (id int identity (1, 1), clientXml xml)

insert into @table (clientXml) values 
('
  
    0.7468
  
'),

('
  
    0.55
  
'),

('
  
    0.76
  
')

select id
      ,clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
from @table t
where t.clientXml.exist(N'(//*/caruwmarketfactor[xs:decimal(text()[1]) gt xs:decimal("0.70")]) 
                       and (//*/caruwmarketfactor[xs:decimal(text()[1]) lt xs:decimal("0.75")])') = 1

UPDATE: I just tried using the same logic (value is between a range) for dates and same problem. Whatever I am doing wrong can be applied to dates too.

exist return 1 if the expression you use returns at least one node. Put your expression in a query and look at what you get back.

select id
      ,clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
      ,clientXml.query(N'(//*/caruwmarketfactor[xs:decimal(text()[1]) gt xs:decimal("0.70")]) 
                          and (//*/caruwmarketfactor[xs:decimal(text()[1]) lt xs:decimal("0.75")])')
from @table t

Result:

id                                                  
----------- --------------------------------------- ---------
1           0.7468000                               true
2           0.5500000                               false
3           0.7600000                               false

You get one node back with the value true or false.

You can change your expression to return the value if it matches by doing the logic in the same predicate something like this.

select T.id,
       T.clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
from @table as T
where T.clientXml.exist('//*/caruwmarketfactor/text()[xs:decimal(.) gt xs:decimal(0.70) and 
                                                      xs:decimal(.) lt xs:decimal(0.75)]') = 1;

The expression within the predicate returns true or false and if it returns true it will return one node to the exist function. If the predicate returns false there will be no node returned.

Logical Expressions (XQuery)

StackExchange Database Administrators Q#152895, answer score: 4