StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO.NET compression of XML to store in SQL Server database
text
Body
copied!<p>Currently our .NET application constructs XML data in memory that we persist to a SQL Server database. The XElement object is converted to a string using ToString() and then stored in a varchar(MAX) column in the DB. We dind't want to use the SQL XML datatype as we didn't need any validation and SQL doesn't need to query the XML at any stage.</p> <p>Although this implementation works fine, we want to reduce the size of the database by compressing the XML before storing it, and decompressing it after retrieving it. Does anyone have any sample code for compressing an XElement object (and decompressing would be great too)? Also, what changes would I need to make to the data type of the database column so that we can fully take advantage of this compression?</p> <p>I have investigated again the XML datatype SQL Server 2005 offers, and the validation overhead it offers is too high for us to consider using it. Also, although it does compress the XML somewhat, it doesn't as much compression as the .NET DeflateStream class.</p> <p>I have tested the DeflateStream class by writing the XML we use to disk, and then saving the comrpessed version as a new file. The results are great, a 16kb file goes down to a 3kb file, so it's jsut a case of getting this to work in memory and saving the resulting data to the DB. Does anyone have any sample code to do the compression, and should I change the varcahr(MAX) colum to type to maybe varbinary?</p> <p>Thanks in advance</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload