Test for a specific node value in an XML column including potentially empty nodes

Question

I have an XML column in a SQL Server 2008 database with values like the following simplified examples:

Case 1

<root>
    <child>sometimes text here</child>
    <otherstuff ..... />
</root>

Case 2

<root>
    <child/>
    <otherstuff ..... />
</root>

Given a string value, I would like to be able to select rows that have a specific value in the "child" node, including selection of case 2.

So for example, if I have a local variable:

declare @queryText nvarchar(MAX)
select @queryText = 'sometimes text here'

I can select the row that matches case 1 by:

select * from [my_table] 
where [my_xml_column].exist('/root/child[text()=sql:variable("@queryText")]') = 1

However, for case 2, where I would expect @queryText = '' or @queryText = NULL to work, neither matches.

As a workaround I can use:

select * from [my_table]
where [my_xml_column].value('(/root/child)[1], 'nvarchar(MAX)') = @queryText

This works, but it leaves me feeling like I'm missing something and using a dirty workaround to test for existence with .value() rather than .exist()... Is there a similar expression I can [and should?] use in .exist() to match either specific text or an empty node? Is there any reason to care beyond readability? I look forward to my impending facepalm when somebody points out whatever blatantly obvious thing I have missed. :)

nathan_jr · Accepted Answer · 2012-06-22T08:36:16.663

Calling text() on an empty element results in NULL not an empty string. So, in both cases passing @queryText = '' or @queryText = NULL will never equal NULL. Remember that nothing equals NULL, not even NULL.

See below example that illustrates how to use exist for populated or empty searches.

declare @my_table table (i int, my_xml_column xml)
insert into @my_table
    select 1, '<root><child>sometimes text here</child><otherstuff /></root>' union all
    select 2, '<root><child/><otherstuff/></root>'

declare @queryText varchar(100) = '';

select  *, 
        [using_text()]=[my_xml_column].value('(/root/child/text())[1]', 'varchar(max)'),
        [using_path]=[my_xml_column].value('(/root/child)[1]', 'varchar(max)')
from    @my_table

select  *
from    @my_table
where   [my_xml_column].exist('/root/child[.= sql:variable("@queryText")]') = 1

Cue the facepalm! :) I could have sworn I tried that [.=sql:variable] form, but I guess not. Works like a charm - thanks! — Rick Riensche, Jun 22 '12 at 15:08
How about for something like this: http://stackoverflow.com/questions/33130499/how-to-replace-text-if-it-exists-otherwise-add-text-to-a-xml-node-using-sql — Si8, Oct 14 '15 at 16:27

Test for a specific node value in an XML column including potentially empty nodes

1 Answers1