• YourVeryOwnSO.sql

-- 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];