possible uses and impact of using xml datatype

I'm looking at this for an application I'm working on right now that is currently using SQL 2000 and a huge number of meta data files.

We synchronize externally using different technologies to items which cannot be pre-defined at all. We don't know what they will look like, what attributes they will have, or even if a new item might pop up.

So. we have a database just to record that "x" exists, and there is an applicaiton layer to interpret with the meta data what x actually means and looks like, then display it to the user. We track changes to "x" once we know it is there, over time. IT's attribute values will change over time.

I'm thinking with 2005, we could stuff the fact that "x" exists into a row, and it's corresponding definition in an XML column. It seems that this is the exact situation the XML data type was invented for.

My question is: am I right in the above assumption, and what would we really gain by moving that information from the filesystem into the database? Better performance? Easier to manipulate the XML? Easier association of a particular XML file to database data? Would it degrade performance of a system that is currently kind of slow but working?

I think if we used this correctly and in a limited way, we could have something pretty spiffy.

How easy is it to read and manipulate the elements in the XML using SQL?

I want to stay away from CLR and continue using the application layer, just have the attributes for the object available to the application layer.

Could someone give me an example of the ideal situation this datatype was invented for? I believe it would be wrong to invent the whole "database-in-a-database" thing, but for our purposes the datatype might work, since we have no control over the entities or attributes but need to store their existence for the UI.

Obviously, I have a lot of research to do but I thought I might ask if it's worth my time at this point.

Yes, it's worth your time to investigate, but it's hard to know what the impact might be on your particular situation.

Tagged data formats and markup languages generally, of which XML is a member, are ideal for metadata situations.

But the thing is, you must already *have* a metadata language in your app, so it's hard to say what benefit would come from changing it to XML ... except for exactly this point, that the xpath and xquery capabilities in XML generally, and the excellent integration with SQL in SQL Server 2005, are very likely to be helpful. Also that XML as a language, is simple, straightforward, and very widespread.