Search code examples
sqlsql-serverxmlsqlxml

Numeric compare of two xml nodes in SQL Server

I have a column XMLRECORD of XML type in a table called ABCD.

My records contain:

<row id="100" xml:space="preserve">
  <c9>400</c9>
  <c10>4000</c10>
</row>

<row id="90" xml:space="preserve">
  <c9>324</c9>
  <c10>34</c10>
</row>

I am trying to compare the two nodes of this XMLRECORD using the query 

SELECT XMLRECORD 
FROM ABCD  
WHERE XMLRECORD.exist(N'/row[c9/text() < c10/text()]') = 1

When executed in SQL Server it displays both the records doing the ASCII compare. How to force the SQL Server to go for a numeric compare?

Solution

  • you can convert it to number inside xpath:

    select *
from ABCD 
where xmlrecord.exist('/row[number((c9/text())[1]) < number((c10/text())[1])]') = 1

    or compare values:

    select *
from ABCD 
where xmlrecord.value('(/row/c9/text())[1]', 'int') < xmlrecord.value('(/row/c10/text())[1]', 'int')