Varchar
(1)
XmlDocument.nodes
(1)
TreeXML
(1)
LimitRequestTree.Item.value
(1)
LimitRequestTree.Item.query
(1)
LimitRequestTree
(1)
XmlDocument
(1)
TreeName
(1)

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

Niels wrote:Can you show us how you set the

Martin Honnen replied to Niels
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/

Problem solved....

Niels replied to Niels
19-Nov-09 09:04 AM
Problem solved.... Comparing strings in XPath is case sensitive and in SQL it
is not.
Post Question To EggHeadCafe