StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTest for a specific node value in an XML column including potentially empty nodes
text
Body
copied!<p>I have an XML column in a SQL Server 2008 database with values like the following simplified examples:</p> <p><strong>Case 1</strong></p> <pre><code><root> <child>sometimes text here</child> <otherstuff ..... /> </root> </code></pre> <p><strong>Case 2</strong></p> <pre><code><root> <child/> <otherstuff ..... /> </root> </code></pre> <p>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. </p> <p>So for example, if I have a local variable:</p> <pre><code>declare @queryText nvarchar(MAX) select @queryText = 'sometimes text here' </code></pre> <p>I can select the row that matches case 1 by:</p> <pre><code>select * from [my_table] where [my_xml_column].exist('/root/child[text()=sql:variable("@queryText")]') = 1 </code></pre> <p>However, for case 2, where I would expect @queryText = '' or @queryText = NULL to work, neither matches.</p> <p>As a workaround I can use:</p> <pre><code>select * from [my_table] where [my_xml_column].value('(/root/child)[1], 'nvarchar(MAX)') = @queryText </code></pre> <p>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. :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload