VastSQL - Thursday, January 18, 2018 1:07 AM

Eirikur Eiriksson - Thursday, January 18, 2018 12:47 AM

VastSQL - Thursday, January 18, 2018 12:34 AM

Hi Experts,

We are creating an archival plan for one of our table which stores json data in nvarchar(max). We planned to load data from original table to a warehouses database on daily basis ,one time a day . The data will be selected  as xml and will zip and then will insert into a table. We were able to select data as xml but when tried to create a assembly to zip the data it threw error.

Msg 10301, Level 16, State 1, Line 11
Assembly 'Zip' references assembly 'netstandard, version=2.0.0.0, culture=neutral, publickeytoken=cc7b13ffcd2ddd51.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

Used this to create the zip dll
https://www.codeproject.com/Articles/27203/GZipStream-Compress-Decompress-a-string.

Does anyone have experience on this or I am I going way off from archiving the data?

Quick thought, the XML data type is quite an efficient storage type, I've only seen around 20-25% effective compression when XML stored in a zipped format. Accessing the data as XML is much easier as having to decompress it before querying it is quite an effort.
😎
Question, why NVARCHAR and not VARBINARY for the zipped XML?

Thanks Eirikur for the reply.. We haven't even reached the stage of zipping . Currently the json is stored as nvarchar and we are converting it to XML and then planning to zip it , now i am stuck at zipping the xml data.