@MichaelGreen +1

Yes, I'm the up-voter.

Hey all.

I need to load some really large (greater than 4 GB limit) XML files into SQL Server 2012 DB, I realize that it's most likely not possible to load anything larger than 4 GB, but is there a workaround, or would it be best to split the XML files into smaller chunks? (as long as it doesn't break the hierarchy, which it won't in my case)

Hey @Phrancis, what happened to your hat? :)

@Phrancis I haven't got much experience with this so maybe it won't work but have you looked into filestream?

@AndriyM It got a little warm ;)

Right :)

@AndriyM I have not

I'm working on putting together a repository that I will eventually post on StackApps, that allows to process and load the SE data dump into SQL Server and parse it into normalized tables

Some of these files from the dump are ridiculously large, like Stack Overflow's PostHistory.xml is 66 GB after unzipping

Took close to an hour to just unzip it

From the manual: FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system.

That was the bit that I have doubts about.

But of course there are other issues, like the fact you need to first enable filestream in your instance.

Hmm, interesting option. Loading these is a once-and-done thing, after they are parsed into actual tables then the XML data can just be deleted, I don't know if it would just add complications to use filestream

That's what I'm thinking too

I might be able to break the XML files down with a batch script or something like that, if they exceed the maximum size

If it's a one-off, my personal choice would probably be to split them.

I don't know much about what you want to do with them, of course

Basically this

DECLARE @XML AS XML;
DECLARE @hDoc AS INT;

SELECT @XML = RawData FROM RawDataXml.Votes;

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML;

SELECT Id, PostId, VoteTypeId, CreationDate
FROM OPENXML(@hDoc, 'votes/row')
WITH (
    Id INT '@Id',
    PostId INT '@PostId',
    VoteTypeId INT '@VoteTypeId',
    CreationDate DATE '@CreationDate'
)
ORDER BY CreationDate DESC;

EXEC sp_xml_removedocument @hDoc;

The structure is really simple, there's no hierarchy to speak of, just a root node then a whole bunch of row nodes with an attribute for each data field/column

@wBob The best of a bad lot (IMO) is Spofford's MDX solutions. There really is no great book on MDX.

Above is a little missive I wrote on DBA.SE, that covers running a SSAS instance. It doesn't go into MDX, though.

MDX looks superficially like SQL but it works very differently.

hello everybody

hi

Yeah! Straight flush on the review queue.

Hmmmm. Requisitioning a new server. I suggest 16 core box with 1.5TB of RAM for the DW.

They come back to me with a 32 core box with 256GB of RAM.

Congratulations you just raised the cost of the box by two fold while simultaneously ensuring I am constantly going to disc.

Brilliant.

@ConcernedOfTunbridgeWells Great, thank you!

so im thinking it'd be a good idea if i had a refreshed on backups

strategies for backups, different ways ppl schedule them and deal with the logs, etc

anyone have any sources on the internet for a good reading?