SQL XPath compare variable with empty XML node

Question

I have the following code:

declare @mydoc xml
declare @myPhone nvarchar(12) = '2223334445'
declare @lastName nvarchar(100) = 'John'
declare @myEmail nvarchar(100) = null
set @mydoc = '<details>
                    <phone />
                    <email />
                    <lastname>John</lastname>
              </details>'


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"))
                                 )]')

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.

If I change my code like below (removed the email part) it works just fine, returns 0.

declare @mydoc xml
declare @myPhone nvarchar(12) = null --'2223334445'
declare @lastName nvarchar(100) = 'John'
declare @myEmail nvarchar(100) = null
set @mydoc = '<lead>
                    <phone />
                    <email />
                    <lastname>John</lastname>
              </lead>'


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)))]')

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.

lptr · Accepted Answer · 2020-04-28T16:03:29.957

declare @mydoc xml;
declare @myPhone nvarchar(12) = '2223334445';
declare @lastName nvarchar(100) = 'JOHN';
declare @myEmail nvarchar(100)= 'TESTmail@mail.org';

set @mydoc = '<details>
                    <phone>123456</phone>
                    <email>testmail@mail.org</email>
                    <lastname>John</lastname>
              </details>';
select @mydoc.exist('
/details[lastname[lower-case(.)=lower-case(sql:variable("@lastName"))]][phone[lower-case(.)=lower-case(sql:variable("@myPhone"))] or email[lower-case(.)=lower-case(sql:variable("@myEmail"))]]');  
go


declare @mydoc xml
declare @myPhone nvarchar(12) = '2223334445'
declare @lastName nvarchar(100) = 'John'
declare @myEmail nvarchar(100) = null
set @mydoc = '<details>
                    <phone />
                    <email />
                    <lastname>John</lastname>
              </details>';


select @mydoc.exist('/details[lastname=sql:variable("@lastName")][phone=sql:variable("@myPhone") or email=sql:variable("@myEmail")]');
---------
set @mydoc = '<details>
                    <phone>2223334445</phone>
                    <email />
                    <lastname>John</lastname>
              </details>'
select @mydoc.exist('/details[lastname=sql:variable("@lastName")][phone=sql:variable("@myPhone") or email=sql:variable("@myEmail")]');
---------
set @myEmail = 'testmail@mail.org'
set @mydoc = '<details>
                    <phone>123456</phone>
                    <email>testmail@mail.org</email>
                    <lastname>John</lastname>
              </details>';
select @mydoc.exist('/details[lastname=sql:variable("@lastName")][phone=sql:variable("@myPhone") or email=sql:variable("@myEmail")]');

It works this way, but you removed the fn:lower-case() functions which I need because from what I've seen, the equality is case sensitive. And this implies using the [1] predicate next to each node name. If I add that in your first example, it still doesn't work. Could you please explain what you thought was wrong? Unfortunately this approach does not fully address my problem, I need to ignore casing on equality. — user2399378, Apr 28 '20 at 15:53
This works if myEmail has value and node has value as well. If you run the first query in your example with the set of data that I provided in my first example, the result is still wrong (1 when it should be 0). From this I understand that the problem is with the fact that no value is provided, but I do not know how to go around that. — user2399378, Apr 28 '20 at 16:06
the problem is that the NULL variable(s) become an empty string in xquery: `declare @t varchar(10)=null;select cast('' as xml).query('concat("the value of @t is:", sql:variable("@t"))')` ... it is easy to filter out "empty" nodes — lptr, Apr 28 '20 at 16:13
Oh my. I get it now. I just changed the ="null" in my initial example with ="" and it worked. Thank you very much. I will also try your example, the code seems a little bit more clean. Do you know if there is another way to check for inequality besides this : not(sql:variable("@myEmail")="null")? — user2399378, Apr 28 '20 at 16:22
https://learn.microsoft.com/en-us/sql/xquery/comparison-expressions-xquery — lptr, Apr 28 '20 at 16:29

SQL XPath compare variable with empty XML node

1 Answers1