SINGLE_BLOB Size Limit: An Affirmation for Bert

Bert left a comment on this post pointing out a limitation of “the other OPENROWSET trick” (the one which loads an XML file into a single XML column):

I tried this, but had an error because my XML file is 6gB. The SINGLE_BLOB seems to be limited to 2gB

Bert is correct; fortunately, this is easily explained. The Books OnLine entry for OPENROWSET points out that a SINGLE_BLOB is an varbinary(max) construct; the documentation for varbinary(max) declares its maximum size to be 2gB.

So you’re right, Bert; this “quick and dirty” method will only work if your XML document is less than 2gB in size. Larger XML documents will need to be loaded into the database through more conventional means.

Thanks for your comment, Bert; now I’ve got two questions for you.. is your 6gB XML document a production sample, or a large document set up for a capacity test? And if it’s the former, can you comment a bit on the design that produced a document this large?

-wp