SQL Server – Import REST API / Read JSON / XML File / Call SOAP (Pagination, OAuth, OData)
Articles,  Blog

SQL Server – Import REST API / Read JSON / XML File / Call SOAP (Pagination, OAuth, OData)


Hello and welcome to ZappySys ODBC PowerPack
101. Today you will learn how to load REST API
data into SQL Server using ZappySys data gateway and ZappySys ODBC drivers. Before starting the presentation let me briefly
tell what ZappySys data gateway is. It’s a tiny SQL Server which gets installed
as Windows service as a part of ZappySys ODBC PowerPack. Basically you can view it as a bridge between
your client application and a data source you want to access. In our case the client application is a SQL
Server and the data source is a REST API service. Actually with ZappySys data gateway you can
also load Soap API data, XML, CSV files, ODBC sources or other ZappySys ODBC driver based
sources. At the end of the video I will go through
other use cases of when to use ZappySys data gateway and tell more about it. Okay so we can start. So suppose you have this REST API service
and it returns customers in JSON format. Many customers, and it returns it as JSON
Array under property value. And you want to load all these customers into
your SQL Server. And it’s almost like you want to write a query
like this, select customers into customer table from this REST API. And actually ZappySys data gateway provides
you with that kind of functionality. The thing you will need to do is just create
a linked server to the gateway. And write a similar query like this and load
the customers into the table. So to start first thing you should do is open
ZappySys data gateway configuration manager. Which is found in Windows start menu in ZappySys
folder. And once you do that create a user. It’s very similar to SQL Server logins and
users. So we will just create one, make it an administrator. And after that you are ready to create a data
source. So click Add, and give it a name, I will name
it OData. And in the connector type there are several
options you can use. So use ODBC generic and if you have a data
source create it in ODBC data source administrator. Just make sure that the data source is created
under system DSN tab. And that you run 64 bit version of ODBC data
source administrator. Because otherwise the gateway won’t be able
to access the data source. And then you can use native ZappySys ODBC
drivers. So it will just create data source right in
the gateway. I should mention that it is as twice fast
as ODBC data source based on ZappySys ODBC drivers so if you have one I suggest switching
to native. So now I’m selecting ZappySys JSON drivers
and since our data is in JSON this box tells me I have to configure the settings. And now I will just have to paste the link
here, go to preview to make sure I get the data. Click OK, I got the data. The problem here is that all customers are
in one row in one cell. So I will just exchange replace this dollar
sign with the column name where the customers are located. To make things easier and quicker you can
also use this list to select data from the populated tables. Okay I’m doing preview again. And okay, so I get the customers as I want,
one customer per row. Okay I’m clicking Okay, then I have to edit
the users. Add the admin, give it full permissions, clicking
okay. Now I have to go to general tab and make note
on what port the gateway is running and make sure SQL Server compatibility check box is
checked too. Okay so once I do that I can just save the
data source and restart the gateway. And then go back to SSMS and add a linked
server. Okay. I will give it a name, gateway, in the provider
SQL Server native client version 11. In the data source enter the address where
your gateway is running, local host in my case. Then put a comma and then enter the port number
on which it runs. In the catalog field enter the data source
name. In our case it’s OData. Then don’t forget to go to security tab and
configure the user. It’s the same user we created and assigned
to the data source. Clicking okay, created the linked server. And now it’s time to execute the query. Okay, but before executing the query let’s
modify it a bit. Gateway. And I will use the same query we used in the
preview. Okay, 20 customers loaded. Checking on that, and voila, and as you see
customers are loaded into the SQL Server. So that’s how easy to run, to load REST API
data into SQL Server using ZappySys data gateway. The next part I will show you how to get data
from products. Not from customers. And how to reuse the same data source but
just override the URL. We start from where we left. So you were using this query and getting customers
very happily. And then you discovered that you need to get
products as well. So the service stays the same, just to the
table name of the OData changes. Meaning the URL changes. And you don’t need to create another data
source, just for products. We can use the data source for customers and
just override the URL. And to do that just go to the gateway, again
configure the data source. We will be overriding this property. And to do that open query builder. And you can override many properties here. We will override URL, we can override let’s
say HTTP request method and basically all properties you saw in the properties tab. I’m clicking okay, I replacing dollar sign
with value to get one product row. Okay. We got the products. And to make things easier we can go to code
generator and copy the T-SQL query it has generated for us. So I’m copying the query, clicking okay. Going back to SMS, based in the query. We need to use our gateway. And let’s execute the query. And you see instead of customers we retrieved
products. So you see how it’s easy to override a URL
for the same data source and reuse it. But it’s not always possible to override a
property and sometimes you have to create another data source. So if that’s the case you have to know that
for that data source you will need to create another linked server. So one linked server for one data source. In the next part I will show you how to parametrize
a query inside a Stored Procedure. So we start from where we left again. And you are querying products and customers
from REST API successfully. And then you notice that throughout all your
database you have these queries repeating themselves in all over the places. And you start thinking that maybe you are
about to create a start procedure. And make it parametric so you have a variable
there and you can define and set what data you want to retrieve, particularly products
or customers. So let’s do that, let’s create a start procedure. So I’m copying this query and pasting it here
in the start procedure template I have. As you see it has a variable, a parameter
table name which will set what URL to retrieve. But only the last part of the URL. And as you see we will define the query, then
we will execute it here. Okay. And the problem now is that we cannot insert
variable here because open query doesn’t allow us that. So we will have to make this whole query dynamic
not only this part. To do that we will need to escape single quote. So what I do I just select the query and then
I replace one single quote with two single quotes. Let’s do that. Okay. And once the single quotes are escaped I can
put the query here. And now I’m ready to make this query dynamic. So namely I can replace this part products
with variable. Table name. Okay, looks good. Let’s select the procedure and create it. Okay. Now we are ready to query the customers. Okay, we retrieved customers. Now let’s do the same for products. And we’ve also got the products. So this concludes the demo of how to load
REST API data into a SQL Server override a URL and execute dynamic queries. In the next part I am going to cover other
use cases and talk about ZappySys data gateway in depth. As I talked in the introduction you can use
ZappySys data gateway as a bridge between your client application and a data source. It uses Microsoft TDS protocol for communication
so you can connect it as you would connect to a Microsoft SQL Server. Therefore any SQL Server compatible driver
can be used to connect to the gateway. For example in JDBC, ADO.net, OLEDB or
ODBC driver. Your client application can be a SQL Server
as you learned today, Pentaho, Java application or any application with any
SQL Server compatible driver. Basically ZappySys data gateway can feed data
to your client app from ODBC data source or a data source based on a ZappySys ODBC driver. In turn, an ODBC data source would get data
from the underlying source, for example a database Excel file etc. And similarly ZappySys ODBC driver would connect
to a REST API, Soap API and XML, CSV or other file, and then retrieve data too. Now let’s move on and let me introduce you
with other use cases of the gateway. You can use ZappySys data gateway in the following
scenarios. In the first scenario your client application
doesn’t support ODBC connectivity. For example it’s a SQL Server and Java application
on a Google spreadsheet. In the second use case you need to access
ZappySys ODBC driver based data source on a non Windows machine. For instance a computer with a Linux Mac Os
or Solaris. So you would install ZappySys ODBC PowerPack
on another machine based on Windows and then connect to ZappySys data gateway from a non
Windows machine. In the third scenario you want all data sources
to be accessed from one place instead of configuring each ODBC data source on every client machine. In the last case you are not allowed to install
third party ODBC drivers on the server due to a compliance reason, simply meaning system
administrator doesn’t allow you to install ZappySys ODBC PowerPack directly on the server
you want to access data on. Therefore you will need to install ODBC Power
Pack on another machine and configure data sources using the gateway. Today you learned how to get data from REST
API and load it into SQL Server using ZappySys data gateway and ZappySys ODBC drivers. At first we executed a simple query and queried
customers, then over wrote the URL of the data source and retrieved products. Finally we created a start procedure with
a parameter which can query any OData table by overriding the URL. Now you can easily use the concepts we went
through to load data into SQL Server from almost any source be it Soap API, XML or CSV
file or ODBC data source. Please share your thoughts and questions in
comments section. Also don’t forget to check other ODBC Power
Pack videos for more use cases. Thanks for watching.

3 Comments

  • ZappySys

    Check this article to learn more about loading REST API data into SQL Server https://zappysys.com/blog/import-rest-api-json-sql-server/

  • Andrey Bolaños

    Hi, I am looking for an app/code that hopefully already exist. I need to drop this code into my server to import data files (XML, JSON, CSV, etc) automatically and according a specific schedule drop the data into a SQL data base. The code has to be scheduled and run automatically to upload aproximately 1000 files a day. Any ideas?

  • ZappySys

    Here are some example queries you can use with this driver
    https://zappysys.com/onlinehelp/odbc-powerpack/scr/json-odbc-driver-sql-query-examples.htm
    https://zappysys.com/onlinehelp/odbc-powerpack/scr/xml-odbc-driver-sql-query-examples.htm
    https://zappysys.com/onlinehelp/odbc-powerpack/scr/csv-odbc-driver-sql-query-examples.htm

Leave a Reply

Your email address will not be published. Required fields are marked *