October 22, 2009 at 8:14 pm
It's easy.
SQL DBA.
October 23, 2009 at 4:49 am
What about book2?
October 23, 2009 at 5:12 am
book2 had a rating less than 3 so wasn't selected 😉
October 23, 2009 at 5:17 am
I believe book2 wasn't selected because the Rating was <3 and the query was looking for books with a rating >=3.
http://brittcluff.blogspot.com/
October 23, 2009 at 5:17 am
I wasn't sure how the exist() method worked, so tried
"where @xml.exist('//Book[Price < 2 ]') = 1"
and got no rows output, so understand now that this the exist() method must look at the whole string, not an individual element - please correct me if I'm wrong 😛
October 23, 2009 at 5:32 am
JTS - yes, I believe you are correct. The exist() method is searching the entire XML document for the argument and is not part of an AND condition for each selection.
http://brittcluff.blogspot.com/
October 23, 2009 at 7:03 am
Good question. I got it wrong but I learned something. Thanks!
October 23, 2009 at 7:59 am
Nice! Would not have expected this. Thanks for the question.
Chad
October 23, 2009 at 8:52 am
I don't understand the "Book[Price < 4 ]"component. Shouldn't that mean that "<Name>book4</Name> " should NOT have been returned since the price of book 4 = 4?
"Beliefs" get in the way of learning.
October 23, 2009 at 9:00 am
Robert Frasca (10/23/2009)
I don't understand the "Book[Price < 4 ]"component. Shouldn't that mean that "<Name>book4</Name> " should NOT have been returned since the price of book 4 = 4?
Your not alone - I thought that too
October 23, 2009 at 9:34 am
Seth Lynch (10/23/2009)
Robert Frasca (10/23/2009)
I don't understand the "Book[Price < 4 ]"component. Shouldn't that mean that "<Name>book4</Name> " should NOT have been returned since the price of book 4 = 4?Your not alone - I thought that too
The condition
where @xml.exist('//Book[Price < 4 ]') = 1
is true if the Price element of *any* Book in the recordset is < 4. book2 satisfies this criteria.
October 23, 2009 at 9:39 am
Thanks for the clarificatin James 🙂
I never use the XML features - but it do have to support them now and then.
October 23, 2009 at 9:40 am
James Rochez (10/23/2009)
Seth Lynch (10/23/2009)
Robert Frasca (10/23/2009)
I don't understand the "Book[Price < 4 ]"component. Shouldn't that mean that "<Name>book4</Name> " should NOT have been returned since the price of book 4 = 4?Your not alone - I thought that too
The condition
where @xml.exist('//Book[Price < 4 ]') = 1is true if the Price element of *any* Book in the recordset is < 4. book2 satisfies this criteria.
Now I'm even more confused because book 2 was the wrong answer and book 1 (with a price of 2) also satisfies that criteria.
"Beliefs" get in the way of learning.
October 23, 2009 at 9:42 am
For those that are having trouble understanding the "Book[Price < 4 ]" component, here is a bit of an explanation.
If you look at the SELECT statement, you'll notice that there is no FROM clause so this query will return at most a single row in the result set. There is only one column returned in the result set, an unnamed column of type xml that contains the result of the query() method. This one row will be returned if the condition(s) of the WHERE clause are satisfied. In english this query would read, if there exists a Book with Price less than 4 then return the Name of all Books/Book with Rating greater than or equal to 3.
Note that that is not the same as returning the Name of all Books/Book with Rating greater than or equal to 3 and Price less than 4.
Also, the explanation isn't quite correct in stating that the query is equivalent to:
SELECT @xml.query('/Books/Book[Rating >= 3]//Name') where 1 = 1
That assumes that @xml contains a Book with Price < 4. For the given example @xml this is true, but the query itself is not equivalent.
The real equivalent code is
IF @xml.exist('//Book[Price < 4 ]') = 1
BEGIN
SELECT @xml.query('/Books/Book[Rating >= 3]//Name')
END
Perhaps you are thrown off by the use of the xml data type and difference between rowsets of data (rows and columns) and XML data that contains its own structure. The WHERE clause applies to the rows, not each element in the XML. Here is an example that demonstrates the same concept using varchar instead of xml.
declare @xml varchar(max)
set @xml='
<Books>
<Book>
<Name>book2</Name>
<Price>3</Price>
<Rating>2.5</Rating>
</Book>
<Book>
<Name>book1</Name>
<Price>2</Price>
<Rating>3</Rating>
</Book>
<Book>
<Name>book3</Name>
<Price>5</Price>
<Rating>4</Rating>
</Book>
<Book>
<Name>book4</Name>
<Price>4</Price>
<Rating>4.5</Rating>
</Book>
</Books>'
SELECT REPLACE(@xml, 'Name', 'Title') where @xml LIKE '%Price>4%'
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply