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.
SELECT Address FROM @tAddress WHERE Address.exist('/AddressType/AddressRecord[ @City = "Houston" ]') =1However....I'm getting errors when I try to use a contains (in the same way I'd use a City Like '%ous%'