What is the difference between inline selection and WHERE

[Niels]

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 don't understand why I dont get the same results in the first
statement as in the second. Can someone please help me understand.

Thanks

[Martin Honnen]

Niels wrote:

> 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.

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/

[Niels]

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