Querying varbinary(max) and xml Columns
CONTAINS and FREETEXT predicates may be used to search indexed varbinary(max) and image as well as xml columns.
|The image data type will be removed in a future version of Microsoft SQL Server. Avoid using this data type in new development work, and plan to modify applications that currently use them. Use the varbinary(max) data type instead.|
Many document types can be stored in a single varbinary(max), or xml column. Microsoft SQL Server 2005 supports certain document types and provides a filter for these types. For a list of these document types, query the sys.fulltext_document_types catalog view.
When a varbinary(max) or an xml column participates in a full-text index, the full-text service looks at the extensions of the documents contained in the varbinary(max) column and applies a corresponding filter to interpret the binary data and extract the textual information needed for full-text indexing and querying. For an xml column, the xml filter is applied.
Once indexed, the varbinary(max) or xml column can be queried like any other column in a table, using the predicates CONTAINS and FREETEXT
For information on how to insert file data into a varbinary(max) or image column, see OPENROWSET (Transact-SQL).