XML Driver / SOAP API Driver for ODBC / SQL ServerODBC XML / SOAP API Driver can be used to read / write data from XML SOAP Web Service (Cloud / Internal Data) or Local XML files. Easily integrate virtually any Web API inside your BI / ETL / Reporting / Tools or Programming Languages. Write familiar SQL queries to read /write data. This driver supports latest API security standards (i.e. OAuth 2.0, JWT, X509), and optimized for large data files. This driver is using same high performance data processing engine which was originally developed for XML / SOAP Connector in SSIS PowerPack. ODBC PowerPack and SSIS PowerPack, both products share many UI elements and concepts. We wrote many articles to explain various features in one product but concepts are mostly same in both products so hope you can reuse steps explained in different articles even though screenshots /steps may be slightly different. Feature Summary
|
||||
|
View All Drivers | |||
Featured Articles
|
Integration Scenarios (Reporting / ETL / BI / Programming)
ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn’t appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.
ZappySys ODBC Drivers – Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell
BI / Reporting Tools
IntegrationETL Tools
Integration
Programming Languages
Integration
ODBC Integration Screenshots in various tools
Access Live SOAP API / XML data inside Microsoft SQL Server (T-SQL)
ZappySys has developed a unique bridge called ZappySys Data Gateway Service (ZSDG) which can help to access our Drivers in SQL Server or JAVA based Apps or Non-Windows OS (e.g. Mac, Linux). ZappySys Data Gateway service can run in the cloud (VM Exposed to internet) or you can install locally on-premises.
Client application can connect to Data Gateway Service using any Microsoft SQL Server compatible driver (i.e. SQL Server ODBC, OLEDB, ADO.net or JDBC Driver or Linked Server in SQL Server). Data Gateway can be installed on the central server where you can have many users who can connect to Data Gateway to use ZappySys Drivers without installing anything on their machine. Data Gateway Service understands TDS Protocol and Client App can be running on any machine or operating system (MacOS, Linux, Windows).
ZappySys Data Gateway – Connect to JSON, XML, OData, REST API, SOAP data sources using TDS protocol compatible drivers (or any SQL Server ODBC, JDBC, OLEDB, ADO.net driver )
Microsoft SQL Server Integration using Data Gateway Service (T-SQL)
Here is one possible use case of using Data Gateway Service. Any DBA or Non-Programmer can start writing T-SQL queries to use ZappySys Drivers (e.g. REST API, JSON, XML, CSV data source) right inside your usual T-SQL code (You can access data from Salesforce, REST API, JSON, XML, CSV inside Views, Functions or SQL Stored Procedures).
This approach can eliminate any possible ETL work needed to extract data outside of SQL Server, you can start using your existing SQL Skill to achieve previously hard to achieve scenarios without coding.
For many other possible use case of Data Gateway click here.
SQL Server Integration Example – Query REST API / JSON Files / XML Files inside SQL Server using ZappySys Data Gateway Service (Use of Linked Server / OPENQUERY Feature in T-SQL Code / SSMS)
Video Tutorial – Calling ZappySys Drivers inside SQL Server (JSON / REST Driver use case)
Here is a short video to demonstrate a use case of Data Gateway. With this approach you can import data from REST API or any other data source for which ZappySys offers Drivers (e.g. Amazon S3, Azure, SFTP, Salesforce, XML , CSV)
SQL Query Examples – XML Driver
/*--------- Basic Read (API, File, Embedded) Query XML API Service ---------*/ SELECT * FROM "feed.entry" WITH ( SRC = 'https://services.odata.org/Northwind/Northwind.svc/Orders' )
/*--------- Basic Read (API, File, Embedded) Query Single File ---------*/ SELECT * FROM $ WITH (SRC = 'C:\Data\cust-1.xml')
/*--------- Basic Read (API, File, Embedded) Query Multiple Files ---------*/ SELECT * FROM $ WITH ( SRC = 'C:\Data\cust*-?.xml' --,RECURSIVE='True' --Include files from sub folder )
/*--------- Basic Read (API, File, Embedded) Query direct XML string (embedded inside query) ---------*/ SELECT * FROM [data.row] WITH ( --enter path in SRC or use static value in DATA' --SRC='http://some/api/data.xml' --SRC='C:\somefile.xml' --SRC='C:\some*.xml' DATA = ' <data> <row><id>1</id><name>AAA</name></row> <row><id>2</id><name>BBB</name></row> </data>', ElementsToTreatAsArray = 'row' --if you don't specify this option then your query may fail when only 1tag is found (use comma for multiple names e.g. order,item,row).
)
/*--------- API Authentication (HTTP,OAuth) Basic Authentication (User Id/Password) ---------*/ --Credentials via Query SELECT * FROM $ WITH ( SRC = 'http://httpbin.org/xml', DataConnectionType = 'HTTP', CredentialType = 'Basic', UserName = 'user1', Password = 'pass123' ) --Credentials inside connection string [Use Data source setup grid >> Select Connection = HTTP (Click Configure) >> Set Credential type=Basic, UserId=user1, Password=pass123] --SELECT * FROM $ WITH (SRC='http://httpbin.org/xml')
/*--------- API Authentication (HTTP,OAuth) NTLM / Windows Authentication ---------*/ --Credentials via Query SELECT * FROM $ WITH ( SRC = 'http://yourhost/some-service', DataConnectionType = 'HTTP', CredentialType = 'Ntlm' ) --Credentials inside connection string [Use Data source setup grid >> Authentication type=Ntlm] --SELECT * FROM $ WITH (SRC='http://yourhost/some-service')
/*--------- API Authentication (HTTP,OAuth) OAuth 2.0 Authentication (3-Legged) - Custom API Example ---------*/ --Credentials via Query SELECT * FROM $ WITH ( SRC = 'http://yourhost/api/some-service', DataConnectionType = 'OAuth', UseCustomApp = 'True', OAuthVersion = 'OAuth2', AuthUrl = 'https://yourhost/api/authorize', TokenUrl = 'https://yourhost/api/token', ClientId = 'xxxxxxxxxxx', ClientSecret = 'xxxxxxxxxxxxx', AccessToken = 'xxxxxxxxxxx', RefreshToken = 'xxxxxxxxxxx' --//This is only obtained from UI by clicking Generate Token Button ) --Credentials inside connection string [Use Data source setup grid >> Authentication type=OAuth >> Click OAuth Button to configure connection] SELECT * FROM $ WITH (SRC = 'http://yourhost/api/some-service')
/*--------- API Authentication (HTTP,OAuth) OAuth 2.0 Authentication (3-Legged) - Google API Example ---------*/ --Credentials via Query --Check this link to learn more about how to generate ClientId / ClientSecret >> https://zappysys.com/blog/register-google-oauth-application-get-clientid-clientsecret/ --To get Access Token and Refresh Token >> Goto properties grid >> Change Authentication type=OAuth >> Click OAuth Button to configure connection >> Select Provider='Google' >> Use Custom App >> Select Scope and click Generate Token >> Save tokens to file SELECT * FROM $ WITH ( SRC = 'https://www.googleapis.com/analytics/v3/metadata/ga/columns', UseCustomApp = 'True', DataConnectionType = 'OAuth', ServiceProvider = 'Google', ClientId = '3855xxxxxx-xxxxxxxxxxxxxxxxx.apps.googleusercontent.com', ClientSecret = 'qF7axxxxxxxxxxxWfs', AccessToken = 'ya29.GlsxxxxxxxxxIam', RefreshToken = '1/pFOxxxxxxxxxxgU' --//This is only obtained from UI by clicking Generate Token Button ) --Credentials inside connection string [Use Data source setup grid >> Authentication type=OAuth >> Click OAuth Button to configure connection] SELECT * FROM $ WITH ( SRC = 'https://www.googleapis.com/analytics/v3/metadata/ga/columns' )
/*--------- API GET (Read Data) Query OData API Service ---------*/ SELECT * FROM "feed.entry" WITH ( SRC = 'https://services.odata.org/Northwind/Northwind.svc/Orders', DataFormat = 'OData' )
/*--------- API Authentication (HTTP,OAuth) Dynamic Token Authentication (2 Steps) - Query SOAP API ---------*/ SELECT * FROM $ WITH( ElementsToTreatAsArray = 'urn:Row', Src = 'https://zappysys.com/downloads/files/test/soap-getdata.aspx', DataConnectionType = 'HTTP', AuthScheme = '{none}', TokenUrl = 'https://zappysys.com/downloads/files/test/soap-login.aspx', TokenRequestData = ' <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:partner.soap.sforce.com"> <soapenv:Body> <urn:login> <urn:username>[$userid$]</urn:username> <urn:password>[$password$]</urn:password> </urn:login> </soapenv:Body> </soapenv:Envelope>', TokenRequestMethod = 'POST', TokenResponseContentFilter = '//*[local-name() = ''sessionid'']', TokenRequestHeaders = 'Content-Type:text/xml|Accept:*/*|Cache-Control:no-cache', TokenResponseContentType = 'Xml', UserName = 'MyUser001', Password = 'P@$$w0rdAAc12', CredentialType = 'TokenDynamic', Filter = '$.soapenv:Envelope.soapenv:Body.urn:Row[*]', RequestData =' <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:partner.soap.sforce.com"> <soapenv:Body> <urn:sessionid>[$token$]</urn:sessionid> </soapenv:Body> </soapenv:Envelope>', Header = 'Content-Type: text/xml;charset=UTF-8 || SOAPAction: "https://zappysys.com/soap-getdata" || Accept: */* || Cache-Control: no-cache', RequestMethod = 'POST' )
/*--------- API Pagination Paginate based on URL Parameter (Loop until certain StatusCode) ---------*/ SELECT * FROM "root.element" WITH ( SRC = 'https://zappysys.com/downloads/files/test/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode', PagingMode = 'ByUrlParameter', PagingByUrlAttributeName = 'page', PagingByUrlEndStrategy = 'DetectBasedOnResponseStatusCode', PagingByUrlCheckResponseStatusCode = 401, IncrementBy = 1 )
/*--------- API Pagination Paginate based on URL Path (Loop until no record) ---------*/ SELECT * FROM $ WITH ( SRC = 'https://zappysys.com/downloads/files/test/cust-<%page%>.xml', PagingMode = 'ByUrlPath', Filter = '$.root.element[*]', PagingByUrlAttributeName = '<%page%>', PagingByUrlEndStrategy = 'DetectBasedOnRecordCount', IncrementBy = 1 )
/*--------- URL JOIN Pass parent URL data to Child URL ---------*/ SELECT c.custid, c.name, c.city, o.orderid, o.orderdate, o.total /* ,i.itemid,i.qty,i.shipped */ FROM $ WITH( --1st level (root)-- src = 'https://zappysys.com/downloads/files/test/join/customers.xml', NextUrlAttributeOrExpr = '$.root.nextpage', ElementsToTreatAsArray = 'element', filter = '$.root.customers.element[*]', alias = 'c', WaitTimeMs = '500' --2nd level-- --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] ), join1_src = 'https://zappysys.com/downloads/files/test/join/c[$c.custid$]/orders.xml', join1_NextUrlAttributeOrExpr = '$.root.nextpage', join1_ElementsToTreatAsArray = 'element', join1_filter = '$.root.orders.element[*]', join1_alias = 'o', join1_WaitTimeMs = '500' /* --3rd level-- --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] and [$o.orderid$]) --You can also use placeholder functions e.g. [$c.firstname,FUN_TRIM$] --see this link for list of functions https://zappysys.com/onlinehelp/odbc-powerpack/scr/odbc-format-static-placeholders.htm ,join2_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/o[$o.orderid$]/items.xml' ,join2_NextUrlAttributeOrExpr='$.root.nextpage' ,join2_ElementsToTreatAsArray='element' ,join2_filter='$.root.items.element[*]' ,join2_alias='i' ,join2_WaitTimeMs='500' --maximum 7 levels deep you can go. Any attributes allowed in WITH clause can be prefixed by by joinN_xxx -- join3_xxxxx, join4_xxxxxx .......... join7_xxxxx */ )
/*--------- URL JOIN Using Placeholder function in Child URL / DATA ---------*/ SELECT b.* FROM $ WITH( --1st level (root)-- src = 'https://zappysys.com/downloads/files/test/nested-base64.xml', filter = '$.soap:Envelope.soap:Body', alias = 'a' --2nd level--, join1_data = '[$a.Result,FUN_BASE64DEC$]', join1_ElementsToTreatAsArray = 'Row', join1_filter = '$.Data.Row[*]', join1_alias = 'b' )
/*--------- URL JOIN Custom Metadata for Child URL and final output ---------*/ SELECT _pid, q._rid, r.custid as CUST_ID, r.city as CUST_CITY, q.VAL1, q.VAL2, q.VAL3, q.DESCR FROM $ ORDER BY _pid, q._rid WITH ( SRC = 'https://zappysys.com/downloads/files/test/join/customers.xml', ElementsToTreatAsArray = 'element', filter = '$.root.customers.element[*]', ALIAS = 'r', JOIN1_DATA = '<ORDERS> <ROW><VAL1>HINTROW</VAL1><VAL2>000100</VAL2><VAL3>000100</VAL3><DESCR>Some notes1</DESCR></ROW> <ROW><VAL1>5023</VAL1><VAL2>000200</VAL2><VAL3>000200</VAL3><DESCR>Some notes2</DESCR></ROW> <ROW><VAL1>0000</VAL1><VAL2>000300</VAL2><VAL3>000300</VAL3><DESCR>Some notes3</DESCR></ROW> </ORDERS>', JOIN1_FILTER = '$.ORDERS.ROW[*]', JOIN1_IncludeParentColumns = 'False', JOIN1_ALIAS = 'q' --metadata for final output (include column, type, length for output of SELECT). --This meta does not perform type casting, Meta = '[ {Name: "_rid",Type: "Int32"}, {Name: "_pid",Type: "Int32"}, {Name: "CUST_ID",Type: "String",Length: 100}, {Name: "CUST_CITY",Type: "String",Length: 100}, {Name: "VAL1",Type: "String",Length: 100}, {Name: "VAL2",Type: "Int32"}, {Name: "VAL3",Type: "String"}, {Name: "DESCR",Type: "String",Length: 254} ]' --child level1 metadata (length not needed). Datatype used below may perform some type casting ---, JOIN1_meta = '[ {Name: "VAL1",Type: "String"}, {Name: "VAL2",Type: "Int32"}, {Name: "VAL3",Type: "String"}, {Name: "DESCR",Type: "String"} ]' )
/*--------- URL JOIN Download files to local disk ---------*/ select a.url, b.data, file_write_binary( base64_to_bytes(b.data), 'c:\temp\' || name ) as bytes_written from $ WITH ( SRC=' https: / / zappysys.com / downloads / files / test / imagelist.xml ', FILTER=' $.doc.list [*] ', ALIAS=' a ', JOIN1_SRC=' [$a.url$] ', JOIN1_ALIAS=' b ', JOIN1_SaveContentAsBinary=' true ' )
/*--------- URL JOIN Download binary files as Base64 and Upload to different URL ---------*/ select a.name, c.* from $ WITH ( --Step1: Get List of URLs to download SRC = 'https://zappysys.com/downloads/files/test/callapi.aspx', METHOD = 'POST', RequestContentTypeCode = 'ApplicationXml', BODY = '<x><row><name>1.png</name></row><row><name>2.png</name></row><row><name>3.png</name></row></x>', FILTER = '$.x.row[*]', ALIAS = 'a', --Step2: Download files as base64 data for each URL JOIN1_SRC = 'https://zappysys.com/images/forums/create-azure-storage-account-get-access-keys-[$a.name$]', --//SRC can be local or URL c:\image.png or http://some/url/file.pdf JOIN1_SaveContentAsBinary = 'true', --//This property returns data as base base64 string rather byte array so its easy to pass in next step JOIN1_METHOD = 'GET', JOIN1_ALIAS = 'b', --Step3: Upload File - POST File Base64 data to target URL JOIN2_SRC = 'https://zappysys.com/downloads/files/test/callapi.aspx', JOIN2_METHOD = 'POST', JOIN2_BODY = '<xml><file><name>[$a.name$]</name><base64>[$b.data$]</base64></file></xml>', JOIN2_ALIAS = 'c' )
/*--------- URL JOIN Query API with Status Check Loop (Wait until value found) ---------*/ SELECT * FROM [root.element] WITH ( SRC='https://zappysys.com/downloads/files/test/cust-1.xml', EnableStatusCheck='True', StatucCheckMaxWaitSeconds=7, StatucCheckIterationWaitSeconds=3, --StatusCheckForFailedValue = 'True', --StatusFailedValue = 'Failed|Cancelled', --StatusFieldFilterExpr='//*[local-name()='Status']', --Use XPath, XmlPath or Regex to narrow down to single property / content under which you like to search --StatusFieldFilterType='Xml', --or use Xml, Regex, None StatusSuccessValue='C1|C2|C3' --Wait until C1 or C2 or C3 found in response (Use Regular expression syntax) ) /* --- See real world example of status check pattern here (Use of XML Driver)... https://zappysys.com/blog/import-bing-ads-data-sql-server-performance-reports/ SELECT c.* FROM $ WITH( --/////////////////////////////////// --Step-1: submit report request for XML format --/////////////////////////////////// alias='a' ,Src='https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc' ,Filter='$.s:Envelope.s:Body.SubmitGenerateReportResponse' ,RequestData='@C:\Requests\BingAds\sql_body1.xml' --Get Body from File for clean Look (Read blog post above to see actual body) ,IsMultiPart='True' ,RequestContentTypeCode='TextXml' ,Header='SOAPAction: "SubmitGenerateReport"' ,RequestMethod = 'POST' ,EnableBodyPlaceholderForMultiPart = 'True' ,Meta = '[{ "Name": "ReportRequestId","Type": "Int64"}]' --/////////////////////////////////// --///Step-2: keep checking status until report is ready - returns final url when done --/////////////////////////////////// ,join1_alias = 'b' ,join1_Filter = '$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus' ,join1_RequestData = '@C:\Requests\BingAds\sql_body2.xml' ,join1_IsMultiPart = 'True' ,join1_Src = 'https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc' ,join1_RequestContentTypeCode = 'TextXml' ,join1_Header = 'SOAPAction: "PollGenerateReport"' ,join1_RequestMethod = 'POST' ,join1_EnableBodyPlaceholderForMultiPart = 'True' ,join1_EnableStatusCheck = 'True', ,join1_StatucCheckMaxWaitSeconds = 300, --wait max 5 mins ? ,join1_StatucCheckIterationWaitSeconds = 5, --check every 5 sec ? ,join1_StatusSuccessValue = 'Success', --look for success word in response --, join1_Meta = '[{ "Name": "ReportDownloadUrl","Type": "String",Length: "500"},{ "Name": "Status","Type": "String",Length: "20"}]' --/////////////////////////////////// --Step-3: Download report(Zip file) and Parse rows --/////////////////////////////////// ,join2_alias = 'c' ,join2_DataConnectionType = 'Default' ,join2_Src = '[$b.ReportDownloadUrl$]' ,join2_Filter = '$.Report.Table.Row[*]' ,join2_ElementsToTreatAsArray = 'Row' ,join2_RequestMethod = 'GET' ,join2_IsMultiPart = 'False' ,join2_FileCompressionType = 'Zip' ) */
/*--------- Metadata Query with static Metadata (META option / Override Mode) ---------*/ SELECT * FROM [data.row] WITH ( DATA = ' <data> <row><id>1</id><checknumber>0001</checknumber><name>AAA</name></row> <row><id>2</id><checknumber>0002</checknumber><name>BBB</name></row> </data>' --//Method-1: >>>DIRECT META (compact mode)<<<, META = 'id:int;checknumber:string(10);name:string(10)' -- Add: @OverrideMode:1 in the column list to override meta for only specific column(s) - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause --,META='@OverrideMode:1;id:int;checknumber:string(10)' --//Method-2: >>>DIRECT META (xml mode)<<< --,META='[{Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10},{Name:"name",Type:"String",Length:10}]' -- Add: @OverrideMode entry in the column list to override meta for only specific column(s). No need to set Type for that entry - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause --,META='[{Name:"@OverrideMode"}, {Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10}]' --//Method-3: >>>META from File <<< --,META='c:\data\meta.xml' --//Method-4: >>>META from Internal Storage - Just refer entry by name <<< --,META='MY_META_STORAGE_NAME' )
/*--------- API POST (Send Data) Simple POST (embedded string) ---------*/ SELECT * FROM $ limit 3 WITH ( METHOD = 'POST', HEADER = 'Content-Type: application/xml || x-hdr1: SomeValue', SRC = 'https://zappysys.com/downloads/files/test/rest-getdata.aspx?postsame=1', BODY = ' <root> <row><id>1</id><name>AAA</name></row> <row><id>2</id><name>BBB</name></row> </root>', Filter = '$.root.row[*]' )
/*--------- API POST (Send Data) Simple POST with Static Output Metadata ---------*/ SELECT * FROM $ WITH ( METHOD = 'POST', HEADER = 'Content-Type: application/xml || x-hdr1: SomeValue', SRC = 'https://zappysys.com/downloads/files/test/rest-getdata.aspx?postsame=1', BODY =' <root> <row><id>1</id><name>AAA</name></row> <row><id>2</id><name>BBB</name></row> </root>', Filter = '$.root.row[*]' --define output metadata using static value like below or from file, META = '[{Name: "id",Type: "Int32"},{Name: "name",Type: "String",Length: 10}]' --,META='id=Int32;name=String(10)' --compact meta format --,META='c:\query_meta.xml' )
/*--------- API POST (Send Data) File Upload data from local file ---------*/ SELECT * FROM $ limit 3 WITH ( METHOD = 'POST', HEADER = 'Content-Type:text/plain||x-hdr1:AAA', SRC = 'http://httpbin.org/post', BODY = '@c:\files\dump.xml', IsMultiPart = 'True' )
/*--------- File Operations Download File from URL (Raw Text) / Write to file / Read from file ---------*/ SELECT FILE_WRITE_TEXT(RAW_DOCUMENT, 'c:\temp\dump.xml') AS ColBytesWritten1 --//text file --,BASE64_TO_FILE('c:\temp\dump.png', FILE_TO_BASE64('http://httpbin.org/image/png') ) AS ColBytesWritten2 --//This is how you can for binary file input must be base64 of data --,FILE_READ_TEXT('c:\temp\dump.txt' ) DiskFileContent FROM $ WITH( Src = 'https://httpbin.org/xml', EnableRawOutputMode = 'True' --//set this flag to read data as RAW text )
REST / SOAP API Call Settings
Our drivers come with easy to use API query builder with many advanced options to connect to virtually any JSON, XML or CSV based data format from local files or API URL.
ZappySys Driver UI – setting to read from File or API URL. Read from JSON REST API / XML SOAP Web Service / CSV Format.
SQL Query Editor / Live Preview / Examples
Our drivers come with easy to use Query editor so you can test any API
ODBC Driver – SQL Query Editor / Live Preview
Security Standards (OAuth, JWT, Basic Auth, X509)
Our ODBC Drivers tested with wide variety of security standards such as OAuth 1.0, OAuth 2.0, Basic Auth, JWT, X509 Certificate. We also support latest encryption standards for secure http (such as SSL/TLS 1.2)
Here is an example of our easy to use OAuth Connection Manager which can be used to connect to any API with OAuth 1.0 or OAuth 2.0 Standards.
OAuth Connection for API Providers (Such as Facebook, Google, Twitter, Linkedin, Amazon, Hubspot, MailChimp…)
Consume data from virtually any API service
Our ODBC API Drivers for JSON, XML and CSV comes with more than 100+ Advanced options to connect to virtually API. Below are few Data sources you may recognize which we have tested to work with our drivers. If your API is not listed below then feel free to contact our Data Integration Team (via support form) and get your question answered quickly.
 |
REST / OData / SOAP API |
 |
XML Files |
 |
Salesforce API |
 |
JSON Files |
 |
Amazon AWS API |
 |
Google Analytics API |
 |
Google BigQuery API |
 |
ElasticSearch API |
 |
Xero API |
 |
eBay API |
 |
Zendesk API |
 |
MailChimp API |
 |
Google DoubleClick API |
 |
HubSpot API |
 |
WorkDay API |
 |
Google AdWords API |
 |
Google Sheet API |
 |
SurveyMonkey API |
 |
Facebook API |
 |
Twitter API |
 |
Office 365 API |
 |
ServiceNow API |
Step-By-Step Video Tutorial (6 Parts Video Series)
Here is full length tutorial if you like to get started with ZappySys Drivers. Below tutorial shows usecase of REST API integration in Excel and Power BI. It covers many topics such as below. We used JSON Driver as an example but many concepts mentioned in below tutorial can be applicable to all other ZappySys drivers.
- Configure ODBC DSN
- Making HTTP GET Calls
- Authenticating REST API requests
- Pagination in REST API
- Building simple and advanced SQL Queries
- POST data to REST API
- Excel Example – Import REST API in Excel
- Power BI Example – Import REST API in Power BI
Articles
Click here to see all articles for [XML File / SOAP API Driver] category
 |
How to read RSS feed in SSIS and ODBC (with pagination)Introduction Read RSS feed in SSIS can be challenging. RSS named first RDF Site Summary and later named Rich Site Summary and Really Simple Syndication allows customer applications to be updated with the news of a site. For example, Microsoft RSS feeds, Apple RSS feeds, Samsung RSS feeds, etc. With RSS the information comes to you directly and you do […] |
 |
Read / Write REST API data in Talend (JSON / XML / SOAP)Introduction In this post we will learn how to read / write REST API data in Talend Open Studio. We will create a simple Talend Job using ZappySys JSON Driver to read from REST API / JSON Files and load into Target (e.g. File / DB). Techniques listed in this article can be also used to read […] |
 |
Get Office 365 data in Power BI using Microsoft Graph API and ODBCIntroduction In this article, we will get Office 365Â data in Power BI using Microsoft Graph API and ODBC drivers. Specifically, we will get Excel file data in OneDrive, events from a Calendar and finally, a list in a note in OneNote and then load that data into a Power BI report. We will achieve […] |
 |
Import REST API in Tableau – Read JSON, SOAP XML, CSVIntroduction Tableau is one of the most popular Reporting / Visualization tool for BI / Data analytics. It comes with many out-of the box connectors to pull data from some popular data sources but still it seriously lakes capability to consume data from millions of other REST / SOAP data sources out there for which […] |
 |
How to call REST API in MicroStrategy (JSON / XML SOAP) Introduction In this new article, we will show how to call REST API in MicroStrategy. MicroStrategy is a Business Intelligence company that provides powerful software to create nice charts, reports to take decisions. It started in 1993 and now it is one of the most popular Business Intelligence tools in the world because it is simple, intuitive and friendly. […] |
 |
Import REST API in MS Access (Load JSON / SOAP XML)Introduction In this post, we will learn how to import REST API in MS Access (JSON or SOAP XML data). We will use ODBC PowerPack to connect and query a JSON file / URL. JSON stands for JavaScript Object Notation and it is an Open and Standard format to read an object with attributes and values. JSON […] |
 |
Connect Workday in Power BI – Import SOAP DataIntroduction In our previous blog post we saw how to import SOAP API data in Power BI. Now let’s continue to explore more use case. In this article we will check how to Connect WorkDay in Power BI. We will show you step by step approach on how to call Workday SOAP API in Power […] |
 |
How to Import Amazon MWS data in Power BIIntroduction In our previous blog we saw how to call REST API in Power BI to import data from JSON based REST API services. Now let’s continue on that and learn how to import Amazon MWS data in Power BI using XML driver. To learn more about how to consume Amazon MWS data in ETL […] |
 |
POST data to API in Informatica using SQL TransformationIntroduction In our previous blog post we saw how to read JSON REST API / XML SOAP in Informatica using ZappySys ODBC Drivers. Now lets look at API Write scenario. We will learn how to to POST data to API in Informatica (Insert / Update). We will use SQL Transformation to fire ODBC Driver Queries […] |
 |
How to read NetSuite data in Power BIIntroduction In our previous blog we discussed how to import REST / SOAP API data in Power BI. Now we will use that knowledge to read NetSuite data in Power BI (NetSuite CRM – SuiteTalk). We will use ZappySys XML Driver to call Search function in NetSuite SOAP API. If you are not familiar with SOAP […] |