Twitter lists with Tweet-SQL 3.6

Here’s a quick post about viewing Twitter lists with Tweet-SQL 3.6. First you can view your lists by running;

EXEC dbo.tweet_lists_list null, null, null;

This will show the lists followed by the authenticating user.

To work with specific list tweets copy the appropriate @list_id into the script below and execute it.

DECLARE @xml XML,
		 @handle INTEGER,
		 @list_id INTEGER,
		 @optional NVARCHAR(50);

  SET @list_id = '17542298';

  -- Turn of resultsets from Tweet-SQL
  EXEC dbo.tweet_cfg_resultset_send 0;

  EXEC dbo.tweet_lists_statuses @list_id, @optional, @xml OUTPUT;

  -- Prepare an xml document
  EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

  SELECT created_at,
		  id,
		  [text],
		  source,
		  truncated,
		  in_reply_to_status_id,
		  in_reply_to_user_id,
		  favorited
  FROM OPENXML (@handle, '/objects/objects', 2)
  WITH
  (
	  created_at NVARCHAR(30),
	  id BIGINT,
	  [text] NVARCHAR(140),
	  source NVARCHAR(100),
	  truncated NVARCHAR(5),
	  in_reply_to_status_id BIGINT,
	  in_reply_to_user_id BIGINT,
	  favorited NVARCHAR(5)
  );

  -- destroy the xml document
  EXEC sp_xml_removedocument @handle;

-- Turn resultsets from Tweet-SQL back on as appropriate
EXEC dbo.tweet_cfg_resultset_send 1;

This will present the last twenty tweets from the list.