I'm using SQL2005. I'm storing addresses in an XML address column, and I need to be able to do partial searches....
For example...

declare @tAddress table (Address xml DEFAULT '<Address Record />' )

INSERT INTO @tAddress (Address) 
	VALUES ('<AddressType name="Shipping">
             <AddressRecord    StreetLine1="111 Main" 
		 	    City="Houston"
			    State="TX"  
                               ZipCode = "11111"  /> 
         </AddressType>  ');

A single address could have multiple address types (billing and shipping) and multiple entries for each one.

I know how to query for an exact match on a column....

SELECT Address FROM @tAddress WHERE Address.exist('/AddressType/AddressRecord[ @City = "Houston"  ]')  =1

However....I'm getting errors when I try to use a contains (in the same way I'd use a City Like '%ous%'

Has anyone written code to query on an XML column using a contains?

Thanks
Kevin