3

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 
('<root>
  <ApplicationData>
    <caruwmarketfactor>0.7468</caruwmarketfactor>
  </ApplicationData>
</root>'),

('<root>
  <ApplicationData>
    <caruwmarketfactor>0.55</caruwmarketfactor>
  </ApplicationData>
</root>'),

('<root>
  <ApplicationData>
    <caruwmarketfactor>0.76</caruwmarketfactor>
  </ApplicationData>
</root>')


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.

4

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)

| improve this answer | |
  • Perfect! the 2nd example is just what I was after. Thank you very much! – John Hennesey Oct 20 '16 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.