SQL Server 2014 - Insert xml data into varbinary field

Question

In my stored procedure, I am creating an XML file which has the potential to be very large, > 1GB in size. The data needs to be inserted into a varbinary column and I was wondering what the most efficient method of doing this is in SQL Server 2014?

If it's XML - why aren't you storing it as `XML` datatype?? Columns of type `XML` also support up to 2 GB in size, and store the XML more efficiently than `varchar(max)` columns — marc_s, May 22 '17 at 20:43
I was storing it in an xml column but have been asked to move it to this new column as a result of a decision outside of my control. — user1698316, May 23 '17 at 07:07
Besides my answer below, what should be faster than `CAST(YourXML AS VARBINARY(MAX))`? — Shnugo, May 23 '17 at 09:59

score 0 · Answer 1 · answered May 22 '17 at 19:51

0

I would look into using a File Table: https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server

And Check out this for inserting blobs: How to insert a blob into a database using sql server management studio

answered May 22 '17 at 19:51

Ben

1,820
2
14
25

I don't have the option to use a file table, unfortunately. – user1698316 May 22 '17 at 19:55
Always thought that OPENROWSET needed a file. Will look into use that. Thanks – user1698316 May 22 '17 at 20:04
What will be calling the stored procedure you are writing? – Ben May 22 '17 at 20:10
A web service. The data used to create the XML file is stored in various tables in the database, so I just want to write in into the varbinary field from the stored proc. – user1698316 May 22 '17 at 20:16

score 0 · Accepted Answer · answered May 23 '17 at 08:19

I was storing it in an xml column but have been asked to move it to this new column as a result of a decision outside of my control

If you have the slightest chance to speak with these persons, you should do this!

You must be aware, that XML is not stored as the string representation you see, but as a hierarchically organized tree. Reading this data or manipulating it is astonishingly fast! If you store the XML as BLOB, you will keep it in its string format (hopefully this is unicode/UCS-2!). Reading this data will need a cast to NVARCHAR(MAX) and then to XML, which means a full parse of the whole document to get the hierarchy tree. When this is done, you can use XML data type methods like .value or .nodes). You will need this very expensive process over and over and over and ...

Especially in cases of huge XMLs (or - even worse - many of them) this is a really bad decision!! Why should one do this??? It will take roughly the same amount of storage space.
The only thing you will get is bad performance! And you will be the one who has to repair this later...

VARBINARY is the appropriate type for data, where you do not care what's inside (e.g. pictures). If these XMLs are just plain archive data and you do not want to read or manipulate them, this can be a choice. But there is no advantage at all!

I agree with your response but I lost the battle and ended up having to store it as a varbinary. — user1698316, Jun 12 '17 at 18:59

SQL Server 2014 - Insert xml data into varbinary field

2 Answers2