When should I use the XML data type in Microsoft SQL Server?
Best Answer
I've done a similar thing where I serialize object data to XML for storage. This removes the burden of having tables, columns, and relationships in place to hold data for complex objects. The process can be helped by using a schema that the result XML conforms to, and the database tables can be used for meta information about the objects in question. In my case, expanding database schema to accommodate the object layout would be a big task!
If you will be doing lots of IO, SQL is your better choice. SQL is designed to work well to get/store data which is why we use it for storing data on something like a website as opposed to XML.
XML is good for human readable data that can be shared and interpreted between applications, as was its intention. XML parsing involves LOTS of string manipulation which can be come ever costly as the size of the data set increases.
Decide on the maximum number of pages your user is expected to browse in 1 session. Do a client fetch that gets set of primary keys that satisfy your maximum criteria and return this set to your client. This process is performed only 1 time. Each time the user requests next or previous page, use the cashed set of keys to get the desired rows based on the page size. This method always retrieves at most n rows where n is the number of rows in your page (after the initial cash retrieval). When the user is done, flush the keys cash. This method is specially useful when you have a complex query where a simple SQL such as "SELECT * FROM ... Where Key > lastKey" won't work. The drawbacks of this approach are:
1 - This method ignores new and removed records after the user has requested initial browse request, however, this is usually acceptable in many types of LOB applications.
2 - This method requires fetching the keys in advance, however, if your max. number of pages is reasonable, this should not be a problem, specially when the query is well-qualified.
Best Answer
I've done a similar thing where I serialize object data to XML for storage. This removes the burden of having tables, columns, and relationships in place to hold data for complex objects. The process can be helped by using a schema that the result XML conforms to, and the database tables can be used for meta information about the objects in question. In my case, expanding database schema to accommodate the object layout would be a big task!