5

The Stack Exchange Data Dump is very large at about 22 GB. The xml file for Stack Overflow's users is around 900 MB (about 100 MB when compressed). The format of this file is something like,

<users>
  <row Id="-1" Reputation="1" CreationDate="2008-07-31T00:00:00.000" DisplayName="Community" LastAccessDate="2008-08-26T00:16:53.810" WebsiteUrl="https://meta.stackexchange.com/" Location="on the server farm" AboutMe="&lt;p&gt;Hi, I'm not really a person.&lt;/p&gt;&#xA;&#xA;&lt;p&gt;I'm a background process that helps keep this site clean!&lt;/p&gt;&#xA;&#xA;&lt;p&gt;I do things like&lt;/p&gt;&#xA;&#xA;&lt;ul&gt;&#xA;&lt;li&gt;Randomly poke old unanswered questions every hour so they get some attention&lt;/li&gt;&#xA;&lt;li&gt;Own community questions and answers so nobody gets unnecessary reputation from them&lt;/li&gt;&#xA;&lt;li&gt;Own downvotes on spam/evil posts that get permanently deleted&lt;/li&gt;&#xA;&lt;li&gt;Own suggested edits from anonymous users&lt;/li&gt;&#xA;&lt;li&gt;&lt;a href=&quot;https://meta.stackexchange.com/a/92006&quot;&gt;Remove abandoned questions&lt;/a&gt;&lt;/li&gt;&#xA;&lt;/ul&gt;&#xA;" Views="649" UpVotes="102041" DownVotes="441996" AccountId="-1" />
  ...
  ...
</users>

I realize that there are user-created scripts for converting this xml to other formats, but I was wondering why it's only available in xml format in the first place? It seems like using csv instead would have generated a smaller, less verbose file...but what do I know? (Spoiler alert: not much)

This is not a feature request. I'm not asking for the data dump to be available in other formats, I just want to understand why xml was chosen as the export format.

1

1 Answer 1

8

I'll try to answer this, we will see if this gets the seal of approval.

Based on the agile principle build the simplest thing that could work I'm pretty sure the team checked for any options that were readily available. As all their data is stored in Microsoft Sql Server is the first thing to check.

Since SqlServer 2005 there is the FOR XML option in select staments. A simple test shows its capabilities.

The sqlcmd tool is capable of running sql statements from the command-line and comes out of the box with the SqlServer client tools.

The following input file, when saved as users.sqlcmd.txt will export the users table in the desired format ...

:xml on
set nocount on
select * 
from StackOverflow.dbo.users 
for xml path('row'), root('users')

... when you execute the following command from the command window...

sqlcmd -S NY-SQLCL03 -E -h -1 -i users.sqlcmd.txt -o users.xml

... to file called users.xml.

All that is now left to do is zip and upload to the Internet Archive.

So this doesn't need any code to be written and runs out of the box on any SqlServer setup.

An xml format has the great benefit that literally every modern development stack has libraries and/or tooling to process xml files. Another benefit is that the XML standard is mature after all this years. This enables the largest possible group of potential users to benefit from this format.

When they upgrade to Sql Server 2016 they might consider switching to JSON

tl;dr; The team probably choose xml as the format for the data-dump because of the tool support in Sql Server as well as the maturity and industry wide acceptance of that format.

2
  • Wow, didn't know that the latest edition of SQL Server would support JSON. I look forward to the day when everything uses JSON instead of XML, lol...that said, now that I look at the xml format used here again, it seems like it wouldn't make much difference when considering xml vs json, it looks like both formats would be about the same size in this case.
    – Bob
    Oct 4, 2015 at 10:29
  • 1
    Ooh, JSON would be nice. I can import JSON natively but I have to write transformation goo to import XML. (But I'd much rather have XML than CSV, for easier direct inspection of the raw data dump!) Oct 4, 2015 at 19:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .