title: SQL XPath compare variable with empty XML node
body: <p>I have the following code:</p>
  
  <pre><code>declare @mydoc xml
  declare @myPhone nvarchar(12) = '2223334445'
  declare @lastName nvarchar(100) = 'John'
  declare @myEmail nvarchar(100) = null
  set @mydoc = '&lt;details&gt;
                      &lt;phone /&gt;
                      &lt;email /&gt;
                      &lt;lastname&gt;John&lt;/lastname&gt;
                &lt;/details&gt;'
  
  
  select @mydoc.exist('/details[fn:lower-case(lastname[1])=fn:lower-case(sql:variable("@lastName"))
                              and (phone[1]=sql:variable("@myPhone") 
                                   or phone[1]=fn:concat(fn:substring(sql:variable("@myPhone") ,1,3),"-",fn:substring(sql:variable("@myPhone") ,4,3), "-", fn:substring(sql:variable("@myPhone") ,7,4))
                                   or (fn:lower-case(email[1])=fn:lower-case(sql:variable("@myEmail")) and not(fn:lower-case(sql:variable("@myEmail"))="null"))
                                   )]')
  </code></pre>
  
  <p>Basically I want to return the details node if it contains the given lastname AND either an email or a phone (provided that they are not empty or null). I would expect the above code to return 0, because even if the lastname = John, the phone is empty and not equal to @myPhone and the email is null. However, it returns 1. </p>
  
  <p>If I change my code like below (removed the email part) it works just fine, returns 0.</p>
  
  <pre><code>declare @mydoc xml
  declare @myPhone nvarchar(12) = null --'2223334445'
  declare @lastName nvarchar(100) = 'John'
  declare @myEmail nvarchar(100) = null
  set @mydoc = '&lt;lead&gt;
                      &lt;phone /&gt;
                      &lt;email /&gt;
                      &lt;lastname&gt;John&lt;/lastname&gt;
                &lt;/lead&gt;'
  
  
  select @mydoc.exist('/lead[fn:lower-case(lastname[1])=fn:lower-case(sql:variable("@lastName"))
                              and (phone[1]=sql:variable("@myPhone") 
                                   or phone[1]=fn:concat(fn:substring(sql:variable("@myPhone") ,1,3),"-",fn:substring(sql:variable("@myPhone") ,4,3), "-", fn:substring(sql:variable("@myPhone") ,7,4)))]')
  </code></pre>
  
  <p>What am I missing about the email part? In the first example I checked that email is not null, because initially I thought that was the problem, but as seen from the second example, the query works fine in the absence of the null check, for the phone node. </p>