SQL query using BLOBS

Question

Today, our configuration accepts XML data and splits this data into multiple tables in SQL. The only advantage of this is good. However, whenever we want to get data, we need to re-link all the data from hundreds of tables to re-export XML. Each XML can be from a few MB to several GB.

We almost never run reports ironically, but very often we extract / save data. Due to dividing it / compiling it into several tables, both saving and searching are not very efficient.

Since the data comes in as XML, I am considering updating our method and saving the XML as a large BLOB in a table. That would be so simple.

Currently, the problem is related to reporting - without the ability to index blobs. I am wondering what parameters I could use as efficient as possible reports.

The database is 100 GB.

I am not a DBA (I am a C # person). I just landed in this post at work, so the only way I could think about this is to do it with C # - build each BLOB as XML and then request the XML data in C #. This, however, would seem to be very inefficient. Maybe XQuery in SQL is better? Although I am not a database administrator, I am more than happy for any suggestions for programming (C # / VB) or SQL.

Answer 1

You can save the data in one XML-type column in your database, and then access the data through XQuery.

XQuery is personally a little inconvenient for me, but I found this list of tips to help:

http://www.jackdonnell.com/?p=266

The advantage is that you save only one version of the data, so updates and reads are fast, except for XML parsing (but this may depend on the amount of data). Retrieving data in a C # database is simple, as you can map your XML to the corresponding SqlDbType .