SQL Server - What is the difference between inline selection and WHERE

Asked By Niels

19-Nov-09 08:02 AM

Hi,

I have a problem regarding inline selecting the right nodes from a tree.

I would really like to write my statement like the following

SELECT
@TreeXML = limitRequestTree.Item.query( '.' ),
@Identifier = limitRequestTree.Item.value( '@Identifier', 'varchar(18)' )
FROM @XmlDocument.nodes( 'TreeExtract/Tree[@Name=sql:variable("@TreeName")
and @State=sql:variable("@State")]' ) AS limitRequestTree(Item)

However sometimes this yields no results.

If I instead write the statement like this

SELECT
@TreeXML = limitRequestTree.Item.query( '.' ),
@Identifier = limitRequestTree.Item.value( '@Identifier', 'varchar(18)' )
FROM @XmlDocument.nodes( 'TreeExtract/Tree' ) AS limitRequestTree(Item)
WHERE limitRequestTree.Item.value( '@Name', 'varchar(18)' ) = @TreeName
AND limitRequestTree.Item.value( '@State', 'char(1)' ) = @State

Then the statement always gives the right results.

I think the first statment looks nicer and seems more like the right way to
do it. And according to the query analyser it should also be faster.
However I just do not understand why I dont get the same results in the first
statement as in the second. Can someone please help me understand.

Thanks

19-Nov-09 08:29 AM

Can you show us how you set the variables
@XmlDocument
@TreeName
@State
when you get different results from those two queries?


--

Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

19-Nov-09 09:04 AM

Problem solved.... Comparing strings in XPath is case sensitive and in SQL it
is not.