Say I have data structures stored as XML (XML data type) within Sql Server. A user wishes to pull out a record if, within the data, a certain string is found.
What are my options for implementing this, and which is the best way to do it?
Note that each record can have different XML data structures
I think that if you think you'll need to search the XML on a regular basis, you would save a lot of pain by extracting some of the fields from the XML into their own columns in the database. Storing the XML as a chunk of text isn't very database friendly - it can't be indexed, cached properly, or used in complex queries easily.
If your Xml is strongly typed, as in you have a schema, then use the XQuery stuff and build views over the top of your Xml columns. The strongly typed stuff isn't a requirement, by any means, but it allows you to build your views in a predicable manner.