gist: Inspired by Brent Ozar's post, here's a Gist to parse SO data. Relationships, keys, and indexes not included. Oh yeah, pay attention to the xml source location as you may need to change it.
http://www.brentozar.com/archive/2014/01/how-to-query-the-stackexchange-databases/, 2014-01-18 20:20:22Z
-- DOWNLOAD YOUR DATA
/*
http://meta.stackoverflow.com/questions/198915/is-there-a-direct-download-link-with-a-raw-data-dump-of-stack-overflow-not-a-t/199303#199303
*/
USE [dbname] -- replace this name, obviously. also, you may need to actually have created the db
go
-- Stage it
SELECT
'Badges' as component,
CAST(bulkcolumn as xml) xdata
INTO dbo.badges_stage
FROM OPENROWSET(
BULK 'd:\xml_files\Badges.xml',
SINGLE_BLOB) AS x;
SELECT
'Posts' as component,
CAST(bulkcolumn as xml) xdata
into dbo.posts_stage
FROM OPENROWSET(
BULK 'd:\xml_files\Posts.xml',
SINGLE_BLOB) AS x;
SELECT
'PostHistory' as component,
CAST(bulkcolumn as xml) xdata
into dbo.posthistory_stage
FROM OPENROWSET(
BULK 'd:\xml_files\PostHistory.xml',
SINGLE_BLOB) AS x;
SELECT
'Users' as component,
CAST(bulkcolumn as xml) xdata
into dbo.users_stage
FROM OPENROWSET(
BULK 'd:\xml_files\Users.xml',
SINGLE_BLOB) AS x;
drop table users_stage
SELECT
'Votes' as component,
CAST(bulkcolumn as xml) xdata
into dbo.votes_stage
FROM OPENROWSET(
BULK 'd:\xml_files\Votes.xml',
SINGLE_BLOB) AS x;
SELECT
'Comments' as component,
CAST(bulkcolumn as xml) xdata
into dbo.comments_stage
FROM OPENROWSET(
BULK 'd:\xml_files\Comments.xml',
SINGLE_BLOB) AS x;
-- shred it
select
x.n.value('(@Id)','bigint') user_id,
x.n.value('(@Reputation)','bigint') user_reputation,
x.n.value('(@CreationDate)','datetime2') user_creation_date,
x.n.value('(@DisplayName)','nvarchar(255)') user_display_name,
x.n.value('(@LastAccessDate)','datetime2') user_last_access_date,
x.n.value('(@Location)','nvarchar(255)') user_location,
x.n.value('(@AboutMe)','nvarchar(max)') user_about_me,
x.n.value('(@Views)','bigint') user_views,
x.n.value('(@UpVotes)','bigint') user_upvotes,
x.n.value('(@DownVotes)','bigint') user_downvotes,
x.n.value('(@EmailHash)','nvarchar(64)') user_email_hash,
x.n.value('(@WebsiteUrl)','nvarchar(255)') user_website
INTO [User]
from users_stage u
cross apply u.xdata.nodes('/users/*') x(n);
select
x.n.value('(@UserId)','bigint') user_id,
x.n.value('(@Name)','nvarchar(255)') badge_name,
x.n.value('(@Date)','datetime2') badge_date
INTO Badge
from badges_stage u
cross apply u.xdata.nodes('/badges/*') x(n);
select
x.n.value('(@Id)','bigint') comment_id,
x.n.value('(@PostId)','bigint') comment_post_id,
x.n.value('(@Text)','nvarchar(max)') comment_text,
x.n.value('(@CreationDate)','datetime2') comment_creation_date,
x.n.value('(@UserId)','bigint') comment_user_id
INTO Comment
from comments_stage u
cross apply u.xdata.nodes('/comments/*') x(n);
select
x.n.value('(@Id)','bigint') vote_id,
x.n.value('(@PostId)','bigint') vote_post_id,
x.n.value('(@VoteTypeId)','bigint') vote_type_id,
x.n.value('(@CreationDate)','datetime2') vote_creation_date
INTO Vote
--x.n.query('.') b
from votes_stage u
cross apply u.xdata.nodes('/votes/*') x(n);
select
x.n.value('(@Id)','bigint') post_id,
--x.n.value('(@PostId)','bigint') votes_post_id,
x.n.value('(@PostTypeId)','bigint') post_type_id,
x.n.value('(@CreationDate)','datetime2') post_creation_date,
x.n.value('(@Score)','bigint') post_score,
x.n.value('(@ViewCount)','bigint') post_view_count,
x.n.value('(@Body)','nvarchar(max)') post_body,
x.n.value('(@OwnerUserId)','bigint') post_owner_user_id,
x.n.value('(@LastActivityDate)','datetime2') post_last_activity_date,
x.n.value('(@Title)','nvarchar(4000)') post_title,
x.n.value('(@AnswerCount)','int') post_answer_count,
x.n.value('(@CommentCount)','int') post_comment_count,
x.n.value('(@FavoriteCount)','int') post_favorite_count
INTO Post
--x.n.query('.') b
from posts_stage u
cross apply u.xdata.nodes('/posts/*') x(n);
select
x.n.value('(@Id)','bigint') posthistory_id,
x.n.value('(@PostHistoryTypeId)','bigint') posthistory_type_id,
x.n.value('(@PostId)','bigint') posthistory_post_id,
x.n.value('(@RevisionGUID)','uniqueidentifier') posthistory_revision_guid,
x.n.value('(@CreationDate)','datetime2') posthistory_creation_date,
x.n.value('(@UserId)','bigint') posthistory_user_id,
x.n.value('(@Text)','nvarchar(max)') posthistory_text
INTO PostHistory
--x.n.query('.') b
from posthistory_stage u
cross apply u.xdata.nodes('/posthistory/*') x(n);
--exit stage left
DROP TABLE [badges_stage];
DROP TABLE [comments_stage];
DROP TABLE [posthistory_stage];
DROP TABLE [posts_stage];
DROP TABLE [users_stage];
DROP TABLE [votes_stage];