XQuery: Finding rows which contain specified xml data
===

I have a table which contains many rows, each with a column named "RecordData" that contains XML data that I wish to search upon.

Three rows worth of example xml data from this column is given below:

1:

    <Record>
      <RecordField ID="31" Name="Barcode1" DataTypeId="5" TypeName="String" Decimals="0" Format="" Mandatory="False">ABC123</RecordField>
    </Record>

2:

    <Record>
      <RecordField ID="15" Name="Field 1" DataTypeId="7" TypeName="Boolean" Decimals="0" Format="" Mandatory="False">true</RecordField>
      <RecordField ID="16" Name="Field 2" DataTypeId="5" TypeName="String" Decimals="0" Format="" Mandatory="False">purpke</RecordField>
    </Record>

3:

    <Record>
      <RecordField ID="15" Name="Field 1" DataTypeId="7" TypeName="Boolean" Decimals="0" Format="" Mandatory="False">true</RecordField>
      <RecordField ID="16" Name="Field 2" DataTypeId="5" TypeName="String" Decimals="0" Format="" Mandatory="False">12</RecordField>
    </Record>




I am using the following SQL to try and find any table rows, that have XML data that contains a specific search term ('1' in this example).

    DECLARE @SearchTerm varchar(max)
    	SET @SearchTerm = '1'
    	SELECT * 
    	FROM MyTableOfData
    	WHERE RecordFields.value('contains( (/Record/RecordField/text())[1],sql:variable("@SearchTerm"))','bit') = 1



As you will see, this relies on the search term appearing in the first "RecordField" element's text, rather than searching over ALL of the "RecordField" nodes.
Meaning, the only result I get back would be row 1, rather than both rows 1 and 3.


I've done a bit of reading through the related MSDN articles, and am having a Google-fail day, as I'm not getting any closer to finding out how to remove the limitation and finally crack this.

Any help would be greatly appreciated :)
