List Subscribers - Fetch
Retrieve Twitter List Subscribers using SQL Server
- See SQLHTTP easy setup for Twitter
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
CREATE PROCEDURE usp_Twitter_List_Subscribers_Fetch( @Profile nvarchar(100), @List_Id bigint, @Slug varchar(50), @Owner_screen_name varchar(500), @Owner_Id bigint, @skip_status bit = 1, @include_entities bit = 1, @count bigint = 20, @cursor bigint = -1, @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @Text_skip_status varchar(5) DECLARE @Text_include_entities varchar(5) SET @Text_skip_status = CASE WHEN @skip_status = 0 THEN 'false' <span style="color: #ff0000"> ELSE 'true' <span style="color: #ff0000"> END SET @Text_include_entities = CASE WHEN @include_entities = 0 THEN 'false' <span style="color: #ff0000"> ELSE 'true' <span style="color: #ff0000"> END DECLARE @URL nvarchar(MAX) DECLARE @QueryString varchar(MAX) DECLARE @HTTPSessionID uniqueidentifier SET @URL = 'https://api.twitter.com/1.1/lists/subscribers.json' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'include_entities', @Text_include_entities, 'cursor', @cursor, 'skip_status', @Text_skip_status, 'slug', @Slug, 'owner_screen_name', @Owner_screen_name, 'owner_id', @Owner_Id, 'count', @count SET @URL = @URL + @QueryString EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_Twitter_Auth_Header @Profile, @HTTPSessionID, @URL, 'GET' EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @ContentType = 'application/json', @Accept = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) EXEC usp_Twitter_List_Subscribers_Fetch @Profile = 'My Twitter', @List_Id = '715919216927322112', @Slug = 'national-parks', @Owner_screen_name = 'twitterdev', @Owner_Id = '2244994945', @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @X xml SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0') --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/users[1]/JsonObject' --list subscribers --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject' --cursors SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'@screen_name', N'nvarchar(MAX)') AS [screen_name] ,T.C.value(N'@friends_count', N'nvarchar(MAX)') AS [friends_count] FROM @X.nodes(N'/JsonObject/users[1]/JsonObject') T(C) --Set @cursor to one of the below values (normally @NextCursor) --and continue calling the above stored procedure to load additional pages DECLARE @NextCursor nvarchar(MAX) DECLARE @PreviousCursor nvarchar(MAX) DECLARE @NextCursorStr nvarchar(MAX) DECLARE @PreviousCursorStr nvarchar(MAX) SELECT @NextCursor = T.C.value(N'@next_cursor', N'nvarchar(MAX)') ,@NextCursorStr = T.C.value(N'@next_cursor_str', N'nvarchar(MAX)') ,@PreviousCursor = T.C.value(N'@previous_cursor', N'nvarchar(MAX)') ,@PreviousCursorStr = T.C.value(N'@previous_cursor_str', N'nvarchar(MAX)') FROM @X.nodes(N'/JsonObject') T(C) END |
|
1 2 3 4 5 6 7 8 |
id screen_name friends_count ----------- ----------------- --------------- 1320935112 sisulele 76 93824302 TsetsegUlzii 256 2912620387 Khaif_School 470 |
- Account Credentials – Verify
- Account Profile – Update
- Account Profile Banner – Remove
- Account Settings – Fetch
- Account Settings – Update
- Direct Message – Delete
- Direct Message – Fetch
- Direct Message – Send
- Direct Message Event – Create
- Direct Message Event – Fetch
- Direct Message Events – Fetch
- Direct Messages – Fetch
- Direct Messages Sent – Fetch
- Favorite – Create
- Favorite – Destroy
- Favorites – Fetch
- Followers (IDs) – Fetch
- Followers – Fetch
- Followers Pending (IDs) – Fetch
- Friends (IDs) – Fetch
- Friends – Fetch
- Friends Pending (IDs) – Fetch
- Friendship – Create
- Friendship – Destroy
- Friendship – Update
- Friendships (My)- Fetch
- Friendships – Fetch
- Friendships No Retweet Users (IDs) – Fetch
- Home – Fetch
- List – Create
- List – Delete
- List – Fetch
- List – Update
- List Member – Create
- List Member – Fetch
- List Member – Remove
- List Members – Create
- List Members – Fetch
- List Members – Remove
- List Subscriber – Create
- List Subscriber – Delete
- List Subscriber – Fetch
- List Subscribers – Fetch
- Lists Memberships – Fetch
- Lists Ownership – Fetch
- Lists Subscriptions – Fetch
- Lists- Fetch
- Mentions – Fetch
- Muted Users (IDs) – Fetch
- Place – Fetch
- Places – Search
- Places By Geocode – Fetch
- Profile Banner – Fetch
- Rate Limit Status – Fetch
- Retweet – Untweet
- Retweeters (IDs) – Fetch
- Retweets – Fetch
- Retweets of Me – Fetch
- Saved Search – Create
- Saved Search – Delete
- Saved Search – Fetch
- Saved Searches – Fetch
- Suggestion Categories – Fetch
- Suggestion Category Users – Fetch
- Trends Available Locations – Fetch
- Trends By Location – Fetch
- Tweet – Delete
- Tweet – Retweet
- Tweet By ID – Fetch
- Tweet- Create
- Tweets By List – Fetch
- Tweets By Query – Fetch
- Tweets By User – Fetch
- User – Block
- User – Fetch
- User – Mute
- User – Report As Spam
- User – Unblock
- User – Unmute
- Users (IDs) Blocked – Fetch
- Users – Fetch
- Users – Search
- Users Blocked – Fetch
- Users Muted – Fetch
- Welcome Message – Create
- Welcome Message – Delete
- Welcome Message – Fetch
- Welcome Message Rule – Delete
- Welcome Message Rule – Fetch
- Welcome Message Rules – Create
- Welcome Message Rules – Fetch
- Welcome Messages – Fetch
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.