Tweet-SQL: Storing searches in a table

A Tweet-SQL user emailed me recently about how to store results from the tweet_src_search procedure in a table. Twitter returns an atom feed for search requests so you have to handle this slightly differently compared to other Tweet-SQL procedures.

Here’s a quick walk-through of how we can get Twitter search results into a database table using Tweet-SQL. First create a table to hold the tweets.

CREATE TABLE dbo.TwitterSearchResults
(
	Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	twitter_id VARCHAR(100),
	published VARCHAR(30),
	title VARCHAR(160),
	content VARCHAR(160),
	updated VARCHAR(30),
	author_name VARCHAR(50),
	uri VARCHAR(100),
	keyword_searched VARCHAR(100)
);

Now we can run the below script to use Tweet-SQL to store twitter search results in the above table. Just change the value set for @keyword.

DECLARE @xml XML,
        @handle INT,
        @keyword VARCHAR(100);

-- Set keyword to search for
SET @keyword = 'Obama'

-- Turn off resultset from Tweet-SQL
EXEC dbo.tweet_cfg_resultset_send 0;

-- Peform a twitter search
EXEC dbo.tweet_src_search @keyword, null, @xml OUTPUT;

-- Prepare an xml document
EXEC sp_xml_preparedocument @handle OUTPUT, @xml, '<root xmlns:a="http://www.w3.org/2005/Atom"></root>';

INSERT INTO dbo.TwitterSearchResults
(
	twitter_id,
	published,
	title,
	content,
	updated,
	author_name,
	uri,
	keyword_searched
)
SELECT [a:id],
	   [a:published],
	   [a:title],
	   [a:content],
	   [a:updated],
	   [author_name],
	   [uri],
	   @keyword -- Store the keyword associated with this search
FROM OPENXML (@handle, '//a:feed/a:entry', 2) -- Path to tweets in the xml docs
WITH
(
	[a:id] VARCHAR(160),
	[a:published] VARCHAR(160),
	[a:title] VARCHAR(160),
	[a:content] VARCHAR(160),
	[a:updated] VARCHAR(160),
	author_name VARCHAR(100) 'a:author/a:name', -- Query further down the xml doc to get author info
	uri VARCHAR(100) 'a:author/a:uri'
 );

-- Clean up
EXECUTE sp_xml_removedocument @handle;

-- Turn on resultset from Tweet-SQL
EXEC dbo.tweet_cfg_resultset_send 1;

Now the TwitterSearchResults table should contain and bunch of tweets.

SELECT *
FROM dbo.TwitterSearchResults;

Get yourself a copy of Tweet-SQL and try this out now!