Introduction
Hello and welcome to our latest blog post!
Thank you for dropping by!
We really appreciate you!
This is one for the "techies" in my audience.
If you have to get data from REST APIs from web sites that deliver services via web sites then this post is for you.
If you are a
manager of such people? You might like to pass this post along.
Ok?
So…..As you know I really like Get Response.
Before I used
Get Response I wrote an emailer in SeETL and I was
sending out emails with SeETL.
I had to remove
people from my list as they asked, I had to remove the bounces, I had to
maintain my list by hand.
Also, I could
not see who opened my emails or clicked my links.
The SeETL
emailer is now available for free if you want to use it.
Basically it allows you to send html emails and it allows
you up to 10 variables to be substituted in to the email.
Not bad for
free I would argue!
But Get
Response, of course, does a WHOLE LOT MORE!!
There are tens
of thousand of companies that use it.
Hundreds of
thousands of small businesses and individuals use it too.
I really like
it and you can get your own 30 day trial on the link
below.
http://www.peternolan.com/likes/GetResponseVideo
One of my
"issues" was that I have 3,700 contacts in my GetResponse
account where I have their linkedin URL in my
personal CRM database.
So I wanted to put the 3,700 linkedin
URLs into my GetResponse account so I could copy the
link and paste it into a web browser to see if my contact had moved before I
sent them an email.
But we all know
how much we "put off" working something like that out.
There is also
the issue that, as good as the reporting is in Get Response, it is operational
style reporting and it does not let you do
segmentation of customers. It does not allow you to integrate your Get Response
data with your other customer data.
So I had these two reasons to want to learn how to both
fetch data from Get Response and send updates in to Get Response.
Now, having
been successful, I will likely integrate Get Response data into the BI4ALL Data
Models at some point in time.
You are welcome
to read through this blog and learn how to get Get
Response data into your data warehouse as well!
And, of course,
if you want me to help you with that I would be pleased to do so.
So, let us
begin!
Firstly, I had
thought I might use C++ to create this REST API reader.
But on
searching I found this page for an SQL Server GIT project that exposes REST
APIs from inside Stored Procedures. Since I wanted to do this on SQL Server I thought I would give this project a try.
https://github.com/geral2/SQL-APIConsumer
The project
contains all the source code that you can download.
Please note. At
the time of writing you need to download Newtonsoft Version 11.0.1 and put that in your dot net
framework directory because this product would need to be recompiled if you use
the later versions.
So where you see the instructions:
STEP 4
Create Assembly Newtonsoft.Json.
If It doesn't exists you need to download and copy it
in this path. Keep in mind that the compiled CLR of this project uses the
version 11.0 of Newtonsoft. If you want to update it you would need to recompiled the code.
CREATE ASSEMBLY [Newtonsoft.Json]
AUTHORIZATION dbo
FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Newtonsoft.Json.dll'
WITH PERMISSION_SET = UNSAFE
You need to
click on this button and download NewtonSoft V11.0.1
and install that in to your dot net framework
directory. That will save you an hour or two!
https://github.com/JamesNK/Newtonsoft.Json/releases/tag/11.0.1
Once it was all installed and working I ran some simple tests like this one.
DECLARE
@httpMethod nvarchar(max) = 'GET'
DECLARE @URL nvarchar(max) = 'https://www.routingnumbers.info/api/name.json?rn=122242597'
DECLARE @Headers
nvarchar(max) = '[{"Name": "Content-Type", "Value" :"text/javascript;
charset=utf-8" }]';
DECLARE
@JsonBody nvarchar(max) = ''
Declare @ts as table
(
Json_Result nvarchar(max),
ContentType varchar(100),
ServerName varchar(100),
Statuscode varchar(100),
Descripcion varchar(100),
Json_Headers nvarchar(max)
)
DECLARE @i AS INT
INSERT INTO @ts EXECUTE @i = [dbo].[APICaller_Web_Extended] @httpMethod ,@URL ,@Headers ,@JsonBody
SELECT * FROM @ts
SELECT
[name]
,[rn]
,[message]
,[code]
FROM (
SELECT Context = Json_Result
from @ts
)tb
OUTER APPLY OPENJSON (context)
WITH
( [name] VARCHAR(20) '$.name'
, [rn] VARCHAR(20) '$.rn'
, [message] VARCHAR(20) '$.message'
, [code] INT '$.code'
);
SELECT *
FROM OPENJSON((select Json_Headers from @ts))
WITH (
Header NVARCHAR(MAX) '$."Name"'
,Value NVARCHAR(MAX) '$."Value"'
) a
I then went on
to go through the Get Response APIs to learn about them and learn how to use
them.
Three important
links you should click are these three.
They take you
to the APIs manual itself, and also to the customer
segmentation APIs manual.
These second
two are well worth reading through.
https://apireference.getresponse.com/?_ga=2.165011446.899509516.1616285960-1979031222.1554191216
https://apidocs.getresponse.com/v3/case-study/search-contacts-guide
https://apidocs.getresponse.com/v3/case-study/segments-manual
So let's start with contacts.
The final code
to download contacts will be below. I will highlight the snippets that you
should read here and explain what they are doing. Obviously all confidental material is removed.
Set the post method and the base URL.
DECLARE
@httpMethod nvarchar(max) = 'GET' ;
DECLARE @URLBASE
nvarchar(max) = 'https://api.getresponse.com/v3/contacts' ;
DECLARE @URL nvarchar(max) ;
DECLARE
@JsonBody nvarchar(max) = '' ;
Set the headers desired. In this case we have only set the API Key name and value and the useQueryString header to true.
DECLARE @Headers
nvarchar(max) = '[{ "Name":
"X-Auth-Token", "Value": "api-key
### Your API Key###" },{ "Name": "useQueryString","Value"
:"true"}]';
Obviously for
larger data sets they will be returned in chunks. In GetResponse
a chunk can be 1 to 1000 rows with a default of 100.
So in testing I created variables that allowed me to test
setting the wsRowsPerPage integer and see the
variations.
I currently
have 6,600+ contacts in my GetResponse account and so
I was playing around with wsRowsPerPage to see how
that varied and how that worked.
Please not the
code has not been designed properly to test for errors and all those things.
This is just prototype code.
In the real code all the variables will be stored in the database and proper testing and data types will be used.
DECLARE @wsTotalCount
integer ;
DECLARE
@wsTotalPages integer ;
DECLARE
@wsCurrentPage integer ;
Declare
@wsLoopCounter integer ;
Declare
@wsRowsPerPage integer ;
set
@wsCurrentPage = 1 ;
set @wsRowsPerPage = 200 ;
Now add the query string for the current page to fetch and the rows per page to fetch.
set @URL = concat (@URLBASE , '?page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage as nvarchar (255))) ;
Create a table for the results of the API call to be returned to. Also declare a variable for the return code from the call.
Declare @ts as table
(
Json_Result nvarchar(max),
ContentType varchar(100),
ServerName varchar(100),
Statuscode varchar(100),
Description varchar(100),
Json_Headers nvarchar(max)
)
DECLARE @i AS INT ;
If the temporary table to hold the test call results exists then drop it.
if object_id('tempdb.dbo.##z01_test1', 'u') is not null
drop table ##z01_test1;
Now perform the actual call to the REST API via the product installed.
INSERT INTO @ts EXECUTE @i = [dbo].[APICaller_Web_Extended] @httpMethod ,@URL
,@Headers ,@JsonBody ;
Get the number of total pages from the response from the API. Note that we presume it worked.
select
@wsTotalPages = a.Value
from
(
SELECT
Header
,Value
FROM (
SELECT Json_Headers
from @ts
)tb
OUTER APPLY OPENJSON (Json_Headers)
WITH
(
Header nvarchar(max) '$.Name'
,Value nvarchar(max) '$.Value'
)
) a
where a.Header = 'TotalPages' ;
Now get the
first page of data from the returned table and insert it into the sql server temporary table. This will create the temporary
table.
I have used nvarchar(max) for testing.
SELECT
contactId
,name
,origin
,timeZone
,activities
,changedOn
,campaignId
,campaignhref
,campaignname
,email
,dayOfCycle
,scoring
,engagementScore
,href
,note
,ipAddress
,latitude
,longitude
,continentCode
,countryCode
,region
,postalCode
,dmaCode
,city
,customFieldId
,customFieldName
,customFieldValue
into
##z01_test1
FROM (
SELECT Json_Result
from @ts
)tb
OUTER APPLY OPENJSON (Json_Result)
WITH
(
contactId nvarchar(max) '$.contactId'
,name nvarchar(max) '$.name'
,origin nvarchar(max) '$.origin'
,timeZone nvarchar(max) '$.timeZone'
,activities nvarchar(max) '$.activities'
,changedOn nvarchar(max) '$.changedOn'
,campaignId nvarchar(max) '$.campaign.campaignId'
,campaignhref nvarchar(max) '$.campaign.href'
,campaignname nvarchar(max) '$.campaign.name'
,email nvarchar(max) '$.email'
,dayOfCycle nvarchar(max) '$.dayOfCycle'
,scoring nvarchar(max) '$.scoring'
,engagementScore nvarchar(max) '$.engagementScore'
,href nvarchar(max) '$.href'
,note nvarchar(max) '$.note'
,ipAddress nvarchar(max) '$.ipAddress'
,latitude nvarchar(max) '$.geolocation.latitude'
,longitude nvarchar(max) '$.geolocation.longitude'
,continentCode nvarchar(max) '$.geolocation.continentCode'
,countryCode nvarchar(max) '$.geolocation.countryCode'
,region nvarchar(max) '$.geolocation.region'
,postalCode nvarchar(max) '$.geolocation.postalCode'
,dmaCode nvarchar(max) '$.geolocation.dmaCode'
,city nvarchar(max) '$.geolocation.city'
,campaignJSON nvarchar(max) '$.campaign' as JSON
,geolocationJSON nvarchar(max) '$.geolocation' as JSON
,customFieldValuesJSON nvarchar(max) '$.customFieldValues' as JSON
,customFieldId nvarchar(max) '$.customFieldValues[0].customFieldId'
,customFieldName nvarchar(max) '$.customFieldValues[0].name'
,customFieldValue nvarchar(max) '$.customFieldValues[0].values[0]'
);
While there are more pages increment the wsCurrentPage and update the URL to be used with the wsCurrentPage.
WHILE (@wsCurrentPage < @wsTotalPages)
BEGIN
set @wsCurrentPage = @wsCurrentPage + 1 ;
set @URL = concat (@URLBASE , '?page=',cast ( @wsCurrentPage
as nvarchar (255)) , '&perPage=' ,cast (
@wsRowsPerPage as nvarchar (255))) ;
Delete the data already sitting in the @ts table. If you don't do this you will get duplicates.
DELETE FROM @ts ;
INSERT INTO @ts
EXECUTE @i = [dbo].[APICaller_Web_Extended] @httpMethod ,@URL ,@Headers ,@JsonBody ;
Now that the
temporary table ##z01_test1 will exist the sql
statement is insert into.
Then the while loop is ended.
insert into ##z01_test1
SELECT
contactId
,name
,origin
,timeZone
,activities
,changedOn
,campaignId
,campaignhref
,campaignname
,email
,dayOfCycle
,scoring
,engagementScore
,href
,note
,ipAddress
,latitude
,longitude
,continentCode
,countryCode
,region
,postalCode
,dmaCode
,city
,customFieldId
,customFieldName
,customFieldValue
FROM (
SELECT Json_Result
from @ts
)tb
OUTER APPLY OPENJSON (Json_Result)
WITH
(
contactId nvarchar(max) '$.contactId'
,name nvarchar(max) '$.name'
,origin nvarchar(max) '$.origin'
,timeZone nvarchar(max) '$.timeZone'
,activities nvarchar(max) '$.activities'
,changedOn nvarchar(max) '$.changedOn'
,campaignId nvarchar(max) '$.campaign.campaignId'
,campaignhref nvarchar(max) '$.campaign.href'
,campaignname nvarchar(max) '$.campaign.name'
,email nvarchar(max) '$.email'
,dayOfCycle nvarchar(max) '$.dayOfCycle'
,scoring nvarchar(max) '$.scoring'
,engagementScore nvarchar(max) '$.engagementScore'
,href nvarchar(max) '$.href'
,note nvarchar(max) '$.note'
,ipAddress nvarchar(max) '$.ipAddress'
,latitude nvarchar(max) '$.geolocation.latitude'
,longitude nvarchar(max) '$.geolocation.longitude'
,continentCode nvarchar(max) '$.geolocation.continentCode'
,countryCode nvarchar(max) '$.geolocation.countryCode'
,region nvarchar(max) '$.geolocation.region'
,postalCode nvarchar(max) '$.geolocation.postalCode'
,dmaCode nvarchar(max) '$.geolocation.dmaCode'
,city nvarchar(max) '$.geolocation.city'
,campaignJSON nvarchar(max) '$.campaign' as JSON
,geolocationJSON nvarchar(max) '$.geolocation' as JSON
,customFieldValuesJSON nvarchar(max) '$.customFieldValues' as JSON
,customFieldId nvarchar(max) '$.customFieldValues[0].customFieldId'
,customFieldName nvarchar(max) '$.customFieldValues[0].name'
,customFieldValue nvarchar(max) '$.customFieldValues[0].values[0]'
);
END
Now lets look at extracting contact
activities. In Get Response you have a default for any transactions. It
defaults to 2 weeks of transactions if you do not tell it otherwise. So say you want to get all the transactions for a contact
when you are first downloading your data.
You can do that
like this.
The URL Base contains the contact ID that has been retrieved from contacts.
DECLARE @URLBASE
nvarchar(max) = 'https://api.getresponse.com/v3/contacts/mkjNxv/activities' ;
It took me a
couple of hours to get the query string set correctly because I had not done
this before. If you had not done this before this is exactly how you set the
query string.
This is saying
get all the contacts activities since 2010-01-01.
A timestamp can also be used so you could fetch contact activities from the prior extraction processing.
set @URL = concat (@URLBASE , '?&query[createdOn][from]=2010-01-01&page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage as nvarchar (255))) ;
The full SP for downloading contact activities is also below.
Summary
So in this blog post I have explained to you how you can get a GitHub open source product to install on your SQL Server machine and to use that product to download your Get Response account. Information.
Footer
Procedure
to update Contact URL
This procedure
is so simple I did not think it needed explaining in the above blog post.
You will have
to find out your custom field ID for the field.
In my case it is "0HSn" but it could be different in your case.
create procedure [dbo].[GR01_update_url_for_contact]
@URLContact nvarchar(max)
,@URLToSet nvarchar(max)
AS
DECLARE
@httpMethod nvarchar(max) = 'POST' ;
DECLARE @Headers
nvarchar(max) = '[{ "Name": "X-Auth-Token",
"Value": "api-key ###Your API
KEY###" }]';
DECLARE
@JsonBody nvarchar(max) ;
set
@JsonBody = '{"customFieldValues": [{"customFieldId":
"0HSn","name": "url","value":["http:\/\/' + @URLToSet + '"]}]}'
Declare @ts as table
(
Json_Result nvarchar(max),
ContentType varchar(100),
ServerName varchar(100),
Statuscode varchar(100),
Descripcion varchar(100),
Json_Headers nvarchar(max)
)
DECLARE @i AS INT
INSERT INTO @ts
EXECUTE @i =
[dbo].[APICaller_Web_Extended]
@httpMethod
,@URLContact
,@Headers
,@JsonBody
return (@i)
GO
Procedure to Download Contact
Data
create procedure [dbo].[GR01_dl_contacts] as
DECLARE
@httpMethod nvarchar(max) = 'GET' ;
DECLARE @URLBASE
nvarchar(max) = 'https://api.getresponse.com/v3/contacts' ;
DECLARE @URL nvarchar(max) ;
DECLARE
@JsonBody nvarchar(max) = '' ;
DECLARE @Headers
nvarchar(max) = '[{ "Name":
"X-Auth-Token", "Value": "api-key
###Your API Key###" },{ "Name": "useQueryString","Value"
:"true"}]';
DECLARE
@wsTotalCount integer ;
DECLARE
@wsTotalPages integer ;
DECLARE
@wsCurrentPage integer ;
Declare
@wsLoopCounter integer ;
Declare @wsRowsPerPage
integer ;
set
@wsCurrentPage = 1 ;
set
@wsRowsPerPage = 200 ;
set @URL = concat (@URLBASE , '?page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast (
@wsRowsPerPage as nvarchar (255))) ;
Declare @ts as table
(
Json_Result nvarchar(max),
ContentType varchar(100),
ServerName varchar(100),
Statuscode varchar(100),
Description varchar(100),
Json_Headers nvarchar(max)
)
DECLARE @i AS INT ;
if object_id('tempdb.dbo.##z01_test1', 'u') is not null drop table
##z01_test1;
INSERT INTO @ts EXECUTE @i = [dbo].[APICaller_Web_Extended] @httpMethod ,@URL
,@Headers ,@JsonBody ;
select
@wsTotalPages = a.Value
from
(
SELECT
Header
,Value
FROM (
SELECT Json_Headers
from @ts
)tb
OUTER APPLY OPENJSON (Json_Headers)
WITH
(
Header nvarchar(max) '$.Name'
,Value nvarchar(max) '$.Value'
)
) a
where a.Header
= 'TotalPages' ;
SELECT
contactId
,name
,origin
,timeZone
,activities
,changedOn
,campaignId
,campaignhref
,campaignname
,email
,dayOfCycle
,scoring
,engagementScore
,href
,note
,ipAddress
,latitude
,longitude
,continentCode
,countryCode
,region
,postalCode
,dmaCode
,city
-- ,campaignJSON
-- ,geolocationJSON
-- ,customFieldValuesJSON
,customFieldId
,customFieldName
,customFieldValue
into
##z01_test1
FROM (
SELECT Json_Result
from @ts
)tb
OUTER APPLY OPENJSON (Json_Result)
WITH
(
contactId nvarchar(max) '$.contactId'
,name nvarchar(max) '$.name'
,origin nvarchar(max) '$.origin'
,timeZone nvarchar(max) '$.timeZone'
,activities nvarchar(max) '$.activities'
,changedOn nvarchar(max) '$.changedOn'
,campaignId nvarchar(max) '$.campaign.campaignId'
,campaignhref nvarchar(max) '$.campaign.href'
,campaignname nvarchar(max) '$.campaign.name'
,email nvarchar(max) '$.email'
,dayOfCycle nvarchar(max) '$.dayOfCycle'
,scoring nvarchar(max) '$.scoring'
,engagementScore nvarchar(max) '$.engagementScore'
,href nvarchar(max) '$.href'
,note nvarchar(max) '$.note'
,ipAddress nvarchar(max) '$.ipAddress'
,latitude nvarchar(max) '$.geolocation.latitude'
,longitude nvarchar(max) '$.geolocation.longitude'
,continentCode nvarchar(max) '$.geolocation.continentCode'
,countryCode nvarchar(max) '$.geolocation.countryCode'
,region nvarchar(max) '$.geolocation.region'
,postalCode nvarchar(max) '$.geolocation.postalCode'
,dmaCode nvarchar(max) '$.geolocation.dmaCode'
,city nvarchar(max) '$.geolocation.city'
,campaignJSON nvarchar(max) '$.campaign' as JSON
,geolocationJSON nvarchar(max) '$.geolocation' as JSON
,customFieldValuesJSON nvarchar(max) '$.customFieldValues' as JSON
,customFieldId nvarchar(max) '$.customFieldValues[0].customFieldId'
,customFieldName nvarchar(max) '$.customFieldValues[0].name'
,customFieldValue nvarchar(max) '$.customFieldValues[0].values[0]'
);
WHILE (@wsCurrentPage < @wsTotalPages)
BEGIN
set @wsCurrentPage = @wsCurrentPage + 1 ;
set @URL = concat (@URLBASE , '?page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast (
@wsRowsPerPage as nvarchar (255))) ;
DELETE FROM @ts ;
INSERT INTO @ts
EXECUTE @i =
[dbo].[APICaller_Web_Extended]
@httpMethod
,@URL
,@Headers
,@JsonBody ;
SELECT ContentType,ServerName,Statuscode,Description FROM @ts
insert into ##z01_test1
SELECT
contactId
,name
,origin
,timeZone
,activities
,changedOn
,campaignId
,campaignhref
,campaignname
,email
,dayOfCycle
,scoring
,engagementScore
,href
,note
,ipAddress
,latitude
,longitude
,continentCode
,countryCode
,region
,postalCode
,dmaCode
,city
-- ,campaignJSON
-- ,geolocationJSON
-- ,customFieldValuesJSON
,customFieldId
,customFieldName
,customFieldValue
FROM (
SELECT Json_Result
from @ts
)tb
OUTER APPLY OPENJSON (Json_Result)
WITH
(
contactId nvarchar(max) '$.contactId'
,name nvarchar(max) '$.name'
,origin nvarchar(max) '$.origin'
,timeZone nvarchar(max) '$.timeZone'
,activities nvarchar(max) '$.activities'
,changedOn nvarchar(max) '$.changedOn'
,campaignId nvarchar(max) '$.campaign.campaignId'
,campaignhref nvarchar(max) '$.campaign.href'
,campaignname nvarchar(max) '$.campaign.name'
,email nvarchar(max) '$.email'
,dayOfCycle nvarchar(max) '$.dayOfCycle'
,scoring nvarchar(max) '$.scoring'
,engagementScore nvarchar(max) '$.engagementScore'
,href nvarchar(max) '$.href'
,note nvarchar(max) '$.note'
,ipAddress nvarchar(max) '$.ipAddress'
,latitude nvarchar(max) '$.geolocation.latitude'
,longitude nvarchar(max) '$.geolocation.longitude'
,continentCode nvarchar(max) '$.geolocation.continentCode'
,countryCode nvarchar(max) '$.geolocation.countryCode'
,region nvarchar(max) '$.geolocation.region'
,postalCode nvarchar(max) '$.geolocation.postalCode'
,dmaCode nvarchar(max) '$.geolocation.dmaCode'
,city nvarchar(max) '$.geolocation.city'
,campaignJSON nvarchar(max) '$.campaign' as JSON
,geolocationJSON nvarchar(max) '$.geolocation' as JSON
,customFieldValuesJSON nvarchar(max) '$.customFieldValues' as JSON
,customFieldId nvarchar(max) '$.customFieldValues[0].customFieldId'
,customFieldName nvarchar(max) '$.customFieldValues[0].name'
,customFieldValue nvarchar(max) '$.customFieldValues[0].values[0]'
);
END
GO
Procedure to Download Contact
Activities
create procedure [dbo].[GR01_dl_contact_activities]
as
DECLARE
@httpMethod nvarchar(max) = 'GET' ;
DECLARE @URLBASE
nvarchar(max) = 'https://api.getresponse.com/v3/contacts/mkjNxv/activities' ;
DECLARE @URL nvarchar(max) ;
DECLARE
@JsonBody nvarchar(max) =
'' ;
DECLARE @Headers
nvarchar(max) = '[{ "Name": "X-Auth-Token",
"Value": "api-key ###Your API
Key###" },{ "Name": "useQueryString","Value"
:"true"}]';
DECLARE
@wsTotalCount integer ;
DECLARE
@wsTotalPages integer ;
DECLARE
@wsCurrentPage integer ;
Declare
@wsLoopCounter integer ;
Declare
@wsRowsPerPage integer ;
set
@wsCurrentPage = 1 ;
set
@wsRowsPerPage = 200 ;
set @URL = concat (@URLBASE , '?&query[createdOn][from]=2010-01-01&page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast (
@wsRowsPerPage as nvarchar (255))) ;
Declare @ts as table
(
Json_Result nvarchar(max),
ContentType varchar(100),
ServerName varchar(100),
Statuscode varchar(100),
Description varchar(100),
Json_Headers nvarchar(max)
)
DECLARE @i AS INT ;
if object_id('tempdb.dbo.##z01_test1', 'u') is not null drop table
##z01_test1;
INSERT INTO @ts EXECUTE @i = [dbo].[APICaller_Web_Extended] @httpMethod ,@URL
,@Headers ,@JsonBody ;
select
@wsTotalPages = a.Value
from
(
SELECT
Header
,Value
FROM (
SELECT Json_Headers
from @ts
)tb
OUTER APPLY OPENJSON (Json_Headers)
WITH
(
Header nvarchar(max) '$.Name'
,Value nvarchar(max) '$.Value'
)
) a
where a.Header
= 'TotalPages' ;
SELECT
activity
,subject
,createdOn
,previewUrl
,resourceId
,resourceType
,resourcehref
,clickTrackid
,clickTrackname
,clickTrackurl
,resourceJSON
,clickTrackJSON
into
##z01_test1
FROM (
SELECT Json_Result
from @ts
)tb
OUTER APPLY OPENJSON (Json_Result)
WITH
(
activity nvarchar(max) '$.activity'
,subject nvarchar(max) '$.subject'
,createdOn nvarchar(max) '$.createdOn'
,previewUrl nvarchar(max) '$.previewUrl'
,resourceId nvarchar(max) '$.resource.resourceId'
,resourceType nvarchar(max) '$.resource.resourceType'
,resourcehref nvarchar(max) '$.resource.href'
,clickTrackid nvarchar(max) '$.clickTrack.id'
,clickTrackname nvarchar(max) '$.clickTrack.name'
,clickTrackurl nvarchar(max) '$.clickTrack.url'
,resourceJSON nvarchar(max) '$.resource' as JSON
,clickTrackJSON nvarchar(max) '$.clickTrack' as JSON
);
WHILE (@wsCurrentPage < @wsTotalPages)
BEGIN
set @wsCurrentPage = @wsCurrentPage + 1 ;
set @URL = concat (@URLBASE , '?&query[createdOn][from]=2010-01-01&page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast (
@wsRowsPerPage as nvarchar (255))) ;
DELETE FROM @ts ;
INSERT INTO @ts
EXECUTE @i =
[dbo].[APICaller_Web_Extended]
@httpMethod
,@URL
,@Headers
,@JsonBody ;
SELECT ContentType,ServerName,Statuscode,Description FROM @ts
insert into ##z01_test1
SELECT
activity
,subject
,createdOn
,previewUrl
,resourceId
,resourceType
,resourcehref
,clickTrackid
,clickTrackname
,clickTrackurl
,resourceJSON
,clickTrackJSON
FROM (
SELECT Json_Result
from @ts
)tb
OUTER APPLY OPENJSON (Json_Result)
WITH
(
activity nvarchar(max) '$.activity'
,subject nvarchar(max) '$.subject'
,createdOn nvarchar(max) '$.createdOn'
,previewUrl nvarchar(max) '$.previewUrl'
,resourceId nvarchar(max) '$.resource.resourceId'
,resourceType nvarchar(max) '$.resource.resourceType'
,resourcehref nvarchar(max) '$.resource.href'
,clickTrackid nvarchar(max) '$.clickTrack.id'
,clickTrackname nvarchar(max) '$.clickTrack.name'
,clickTrackurl nvarchar(max) '$.clickTrack.url'
,resourceJSON nvarchar(max) '$.resource' as JSON
,clickTrackJSON nvarchar(max) '$.clickTrack' as JSON
);
END
GO
With that?
I would like to
say thank you very much for reading this blog post!
I hope you
liked it.
If you need any
help getting your Get Response data down from your account and you would like
to pay me for my time?
I am more than
happy to help out!
Best Regards
Peter.
