Mailing Lists - Fetch
Retrieve a list of mailing lists using SQL Server
- See SQLHTTP easy setup for Mailgun
- 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 |
CREATE PROCEDURE usp_Mailgun_Mailing_Lists_Page_Fetch( @Profile nvarchar(100), @Limit int = 100, @CurrentURL nvarchar(MAX) = NULL, @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @QueryString varchar(MAX) DECLARE @Password nvarchar(4000) DECLARE @HTTPSessionID uniqueidentifier IF @CurrentURL IS NULL BEGIN SET @CurrentURL = 'https://api.mailgun.net/v3' EXEC SQLHTTP.net.UrlBuilder @CurrentURL OUTPUT, @Profile, 'lists','pages' END EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'limit', @Limit SET @CurrentURL = @CurrentURL + @QueryString EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT SET @Password = SQLHTTP.net.AuthParam(@Profile, 'APIKey') EXEC SQLHTTP.net.BasicAuthHeader @HttpSessionID, 'api', @Password EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @CurrentURL, @Method = 'GET', @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 52 |
DECLARE @CurrentURL nvarchar(MAX) DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) EXEC usp_Mailgun_Mailing_Lists_Page_Fetch @Profile = 'My Mailgun', @Limit = 100, @CurrentURL = @CurrentURL, @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/items[1]/JsonObject' -- mailing lists --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/paging' -- URL page navigation SELECT T.C.value(N'@access_level', N'nvarchar(MAX)') AS [access_level] ,T.C.value(N'@address', N'nvarchar(MAX)') AS [address] ,T.C.value(N'@name', N'nvarchar(MAX)') AS [name] ,T.C.value(N'@description', N'nvarchar(MAX)') AS [description] ,T.C.value(N'@member_count', N'nvarchar(MAX)') AS [member_count] FROM @X.nodes(N'/JsonObject/items[1]/JsonObject') T(C) --Set @CurrentURL to one of the below values (normally @NextURL) --and continue calling the above stored procedure to load additional pages DECLARE @FirstURL nvarchar(MAX) DECLARE @LastURL nvarchar(MAX) DECLARE @NextURL nvarchar(MAX) DECLARE @PreviousURL nvarchar(MAX) SELECT @FirstURL = T.C.value(N'@first', N'nvarchar(MAX)') ,@LastURL = T.C.value(N'@last', N'nvarchar(MAX)') ,@NextURL = T.C.value(N'@next', N'nvarchar(MAX)') ,@PreviousURL = T.C.value(N'@previous', N'nvarchar(MAX)') FROM @X.nodes(N'/JsonObject/paging') T(C) END |
1 2 3 4 5 |
access_level address name description member_count -------------- -------------------- ---------- ------------------------- ---------------- readonly dev@sandbox03fb... Dev List Mailgun developers list 0 |
- Bounce – Create
- Bounce – Delete
- Bounce – Fetch
- Bounce List – Delete
- Bounces – Fetch
- Complaint – Create
- Complaint – Delete
- Complaint – Fetch
- Complaints – Fetch
- Event Stats – Fetch
- Events – Fetch
- Mailing List – Create
- Mailing List – Delete
- Mailing List – Fetch
- Mailing List – Update
- Mailing List Member – Create
- Mailing List Member – Delete
- Mailing List Member – Update
- Mailing List Members – Create
- Mailing List Members – Fetch
- Mailing Lists – Fetch
- Tag – Fetch
- Tags – Fetch
- Text Message – Send
- Unsubscribe – Create
- Unsubscribe – Delete
- Unsubscribe – Fetch
- Unsubscribes – Fetch
- Webhook – Create
- Webhook – Delete
- Webhook – Fetch
- Webhook – Update
- Webhooks – 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.