Databases
StackOverflow2013
Postsdbo
POSQL Server XML exist()
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server XML exist()
    text
    copied!<p>I have some problems using the <code>exist()</code> and <code>value()</code> methods in SQL Server 2008.</p> <p>My XML looks like this:</p> <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;library&gt; &lt;branches&gt; &lt;branch&gt; &lt;codelib&gt;1&lt;/codelib&gt; &lt;name&gt;Campus&lt;/name&gt; &lt;/branch&gt; &lt;branch&gt; &lt;codelib&gt;2&lt;/codelib&gt; &lt;name&gt;47th&lt;/name&gt; &lt;/branch&gt; &lt;branch&gt; &lt;codelib&gt;3&lt;/codelib&gt; &lt;name&gt;Mall&lt;/name&gt; &lt;/branch&gt; &lt;/branches&gt; &lt;books&gt; &lt;book type="SF"&gt; &lt;codb&gt;11&lt;/codb&gt; &lt;title&gt;Robots&lt;/title&gt; &lt;authors&gt; &lt;author&gt;author1 robots&lt;/author&gt; &lt;author&gt;author2 robots&lt;/author&gt; &lt;/authors&gt; &lt;price&gt;10&lt;/price&gt; &lt;stocks&gt; &lt;branch codelib="1" amount="10"/&gt; &lt;branch codelib="2" amount="5"/&gt; &lt;branch codelib="4" amount="15"/&gt; &lt;/stocks&gt; &lt;from&gt;20&lt;/from&gt; &lt;to&gt;30&lt;/to&gt; &lt;/book&gt; &lt;book type="poetry"&gt; &lt;codb&gt;12&lt;/codb&gt; &lt;title&gt;Poetry book&lt;/title&gt; &lt;authors&gt; &lt;author&gt;AuthorPoetry&lt;/author&gt; &lt;/authors&gt; &lt;price&gt;14&lt;/price&gt; &lt;stocks&gt; &lt;branch codelib="1" amount="7"/&gt; &lt;branch codelib="2" amount="5"/&gt; &lt;/stocks&gt; &lt;from&gt;25&lt;/from&gt; &lt;to&gt;40&lt;/to&gt; &lt;/book&gt; &lt;book type="children"&gt; &lt;codb&gt;19&lt;/codb&gt; &lt;title&gt;Faitytales&lt;/title&gt; &lt;authors&gt; &lt;author&gt;AuthorChildren&lt;/author&gt; &lt;/authors&gt; &lt;price&gt;20&lt;/price&gt; &lt;stocks&gt; &lt;branch codelib="1" amount="10"/&gt; &lt;branch codelib="3" amount="55"/&gt; &lt;branch codelib="4" amount="15"/&gt; &lt;/stocks&gt; &lt;from&gt;70&lt;/from&gt; &lt;to&gt;75&lt;/to&gt; &lt;/book&gt; &lt;book type="literature"&gt; &lt;codb&gt;19&lt;/codb&gt; &lt;title&gt;T&lt;/title&gt; &lt;authors&gt; &lt;author&gt;A&lt;/author&gt; &lt;/authors&gt; &lt;price&gt;17&lt;/price&gt; &lt;stocks&gt; &lt;branch codelib="1" amount="40"/&gt; &lt;/stocks&gt; &lt;from&gt;85&lt;/from&gt; &lt;to&gt;110&lt;/to&gt; &lt;/book&gt; &lt;/books&gt; &lt;/library&gt; </code></pre> <p>Given this XML, I have to write a <code>SELECT</code> clause that will use <code>query()</code>, <code>value()</code> and <code>exist()</code> 2 times each, minimum. I can't even use <code>query()</code> and <code>exist()</code> in the same <code>SELECT</code>, as it appears that the <code>WHERE</code> clause has no effect whatsoever. </p> <p>For example, I want to retrieve all the <code>&lt;branch&gt;</code> elements that are children of the book with the type <code>SF</code>, but the select statement</p> <pre><code> declare @genre varchar(15) set @genre = 'SF' SELECT XMLData.query('//branch') from TableA WHERE XMLData.exist('//book[./@type = sql:variable("@genre")]') = 1 </code></pre> <p>retrieves all the <code>&lt;branch&gt;</code> elements, not just the ones from the targeted book. I can't figure out what's wrong with my select. Also, I would appreciate a small example with <code>query()</code>, <code>exist()</code> and <code>value()</code> in the same select (is it possible to have nested select statements in sql xml?)</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