What's the difference between these 2 SQL Server XQuery queries?

We have a column in a table that contains xml and is setup as a non-schema based xml column

The data can contain multiples instance of a node that looks like this:

<eq:Fund>
  <v:FundCode>
    <v:type>SEDOL</v:type>
    <v:value>3049141</v:value>
  </v:FundCode>
</eq:Fund>
<eq:Fund>
  <v:FundCode>
    <v:type>Product Provider Specific</v:type>
    <v:value>CASH</v:value>
  </v:FundCode>
</eq:Fund>

This query returns 448 rows:

 WITH XMLNAMESPACES ('http://schema1' as eq, 'http://schema2' as v, DEFAULT 'http://schema3')
select * from xml_request
WHERE xml_request_body.value('contains( string( (//v:type)[1] ),"SEDOL")','bit') = 1

This query returns 784 rows (after removing blanks in the result column)

    WITH XMLNAMESPACES ('http://schema1' as eq, 'http://schema2' as v, DEFAULT 'http://schema3')
select xml_request.xml_request_ser, xml_request.agency_number, xml_request.policy_holder, xml_reque开发者_如何学运维st.product,
xml_request_body.query('for $x in /eq:EQuote/eq:Request/eq:Fund where $x/v:FundCode/v:type = "SEDOL" return $x') as result
from xml_request

1) Why do they return different number of rows? The first query doesn't find all the rows where the element contains SEDOL? Why not?

I'm guessing it may be due to the fact that the element with SEDOL may not be the first? 2) How do I change the first query to look through all elements not just the first?

The [1] in the first means that it explicitly requires the first type element (in document order) to contain SEDOL, in order for a match to be made. So yes, if as you say some rows have a non-SEDOL FundCode first, they won't match.

What you want to do it the first way is

WHERE xml_request_body.exist('//v:type[contains(., "SEDOL")]') = 1

This looks for any v:type element such that the text contains SEDOL.