Integrate MYOB or Xero with SQL Server using OdataLink CLR

OdataLink provides a simple method to integrate MYOB and Xero with SQL Server. The OdataLink Common Language Runtime library (CLR) provides simple functions used to download from any OData Feed. It can be added to any SQL Server on premise installation.

Note that this approach is only compatible with Microsoft SQL Server and Azure SQL Managed Instance (untested). This is because these platforms include the ability to integrate with the CLR.

The OdataLink CLR is not compatible with SQL Azure Database.

This article will explain everything you need to know to integrate MYOB and Xero with SQL Server.

OdataLink as an OData provider

OdataLink is an OData provider that converts the internal data in MYOB and Xero into OData Feeds. Many OData client can integrate with these OData Feeds directly.

While SQL Server can consume this data using SQL Server Integration Services (SSIS), doing so can prove difficult and technical to implement. It requires a high-level of understanding and skill with SSIS.

To simplify the integration process, we provides a library (or dll) you can add to SQL Server. This library can greatly simplify accessing OData feeds directly from views and stored procedures. It is aimed at database administrators and developers with skill in Transact-SQL.

So as to keep it simple and lightweight, it only provides function to download data from OData Feeds. You can use the in-built JSON support within SQL Server and Transact-SQL to transform this data.

This library is currently provided free-of-charge and as-is.

The OdataLink CLR library for SQL Server

The OdataLink CLR library is a simple DLL that provides two basic functions.

GetODataPages
This function returns all pages of information for a given OData feed. This function is the go to function to use when going live.

GetODataPage
This function returns a single page of information for a given OData feed. This function is primarily used when testing and developing as it will not return all information. It is also useful for live environment when the GetODataPages function returns too much data to process at once.

These functions, once registered on your SQL Server database, are used to download data from OdataLink. They can be used directly within Views and Stored Procedure.

In addition, this library is not restricted to OdataLink and can actually be used with any OData Feeds.

Adding the OdataLink CLR library to SQL Server

These instructions can be used for SQL Server (on premise). They cannot be used for Azure SQL Managed Instance.

First, download the OdataLink CLR library from the following link and save it to your SQL Server machine.

https://odatalink.com/downloads/sql-server/OdataLinkCLR/OdataLinkCLR.dll

Second, download and run the following installation script. You will need to modify the scripts @InstallPath variable to match the location where you downloaded the library.

https://odatalink.com/downloads/sql-server/OdataLinkCLR/Install-OdataLinkCLR.txt

Once the script runs, you will see the following items within Microsoft SQL Server Management Studio.

• GetODataPage and GetODataPages functions under Programmability \ Functions \ Scalar-Value Functions
• OdataLinkCLR assembly under Programmability \ Assemblies

Adding the OdataLink CLR to an Azure SQL Managed Instance

These instructions can be used for Azure SQL Managed Instance and SQL Server (on premise). Note that we’ve only tested them for SQL Server.

First, download the OdataLink CLR library from the following link and save it on your desktop in a folder called C:\OdataLinkCLR\.

https://odatalink.com/downloads/sql-server/OdataLinkCLR/OdataLinkCLR.dll

Secondly, you will need to create a code signing certificate and digitally signed the OdataLinkCLR.dll. We’ve provided the following batch file script which you can customise if required. This script will create the Certificate, Private Key and PFX bundle required to digitally signed the OdataLinkCLR.dll

@ECHO OFF

CLS

REM ------------------------------------------------------------------------------------------
REM GO TO THE C: drive and the windows SDK folder. Note that your folder may be different.
REM ------------------------------------------------------------------------------------------

C:
CD "C:\Program Files (x86)\Windows Kits\10\bin\10.0.19041.0\x64"


REM ------------------------------------------------------------------------------------------
REM Create the certificate and private key
REM ------------------------------------------------------------------------------------------

makecert -r -pe -n "CN=OdataLink CLR Certificate" -a sha256 -sky signature -cy authority -sv "C:\OdataLinkCLR\OdataLinkCLRCert.pvk" -len 2048 -m 144 "C:\OdataLinkCLR\OdataLinkCLRCert.cer"

REM ------------------------------------------------------------------------------------------
REM Create the PFX file
REM ------------------------------------------------------------------------------------------

PVK2PFX -pvk "C:\OdataLinkCLR\OdataLinkCLRCert.pvk" -spc "C:\OdataLinkCLR\OdataLinkCLRCert.cer" -pfx "C:\OdataLinkCLR\OdataLinkCLRCert.pfx"

REM ------------------------------------------------------------------------------------------
REM Sign the OdataLink CLR with the certificate created.
REM ------------------------------------------------------------------------------------------

signtool sign /f "C:\OdataLinkCLR\OdataLinkCLRCert.pfx" "C:\OdataLinkCLR\OdataLinkCLR.dll"

Thirdly, you will need to extract the binary from the certificate, private key and dll in order to add add them to SQL Server. The following script can assist.

-- The following script will extract the binary content from all three files for use in other scripts.
SELECT '0x0_binary_from_cert' AS [Type], BulkColumn AS [FileContent] 
FROM OPENROWSET(BULK N'C:\OdataLinkCLR\OdataLinkCLRCert.cer' , SINGLE_BLOB) AS result

UNION ALL SELECT '0x0_binary_from_pvk'  AS [Type], BulkColumn AS [FileContent] 
FROM OPENROWSET(BULK N'C:\OdataLinkCLR\OdataLinkCLRCert.pvk', SINGLE_BLOB) AS result

UNION ALL SELECT '0x0_binary_from_dll'  AS [Type], BulkColumn AS [FileContent] 
FROM OPENROWSET(BULK N'C:\OdataLinkCLR\OdataLinkCLR.dll', SINGLE_BLOB) AS result

Fourth, you will need to add the certificate and login to SQL Server’s Master database.

-- the certificate and login must be added to the master database.
USE master

-- create the certificate
-- make sure to edit and paste the binary from 0x0_binary_from_cert
CREATE CERTIFICATE [OdataLinkCLRCert]
FROM BINARY = 0x0_binary_from_cert

-- create the login
CREATE LOGIN [OdataLinkCLRCertLogin] FROM CERTIFICATE [OdataLinkCLRCert];

-- ensure it has unsafe permissions in order to get access to network/internet
GRANT UNSAFE ASSEMBLY TO [OdataLinkCLRCertLogin];

And lastly, you will need to add the assembly and functions to your SQL Server’s database.

-- select your database
-- make sure to edit the YourDatabaseName
USE YourDatabaseName

-- register the dll located at the installation path
-- make sure to edit and paste the 0x0_binary_from_dll from script
CREATE ASSEMBLY OdataLinkCLR FROM 0x0_binary_from_dll WITH PERMISSION_SET = UNSAFE;
GO

-- create the function to get odata
Create Function GetODataPage(@URL nvarchar(max), @UserName nvarchar(max), @Password nvarchar(max) )
RETURNS nvarchar(max)
AS External name OdataLinkCLR.[OdataLinkCLR.OdataLinkCLRFunctions].GetODataPage;
GO

-- create the function to get odata
Create Function GetODataPages(@URL nvarchar(max), @UserName nvarchar(max), @Password nvarchar(max) )
RETURNS nvarchar(max)
AS External name OdataLinkCLR.[OdataLinkCLR.OdataLinkCLRFunctions].GetODataPages;
GO

Using the OdataLink CLR functions

Both GetODataPage and GetODataPages functions accept 3 parameters:

• URL
  The OData Feed url.
• Username
  The username if using Basic Authentication. Leave blank if using Anonymous Authentication.
• Password
  The password if using Basic Authentication. Leave blank if using Anonymous Authentication.

These functions, used with SQL Server’s native JSON support, allows you to download and parse the data. These functions are used directly within Stored Procedure or Views to establish a live link with Xero or MYOB.

They work by using the native OPENJSON and JSON_VALUE functions in SQL Server. These functions use JSON Path Expressions to navigate through the data.

You can use the functions in conjunction with the OPENJSON as per the following example. This example uses the WITH clause to extract the DataFileID, Type, InvoiceNumber and Date fields. This is the approach we recommend.

SELECT
	[Invoices].[DataFileID],
	[Invoices].[Type],
	[Invoices].[InvoiceNumber],
	[Invoices].[Date]
FROM OPENJSON(dbo.GetODataPages('url', 'username', 'password'), '$.value')
WITH
(
	[DataFileID] uniqueidentifier '$.DataFile.DataFileID', 
	[Type] nvarchar(20) '$.Type',
	[InvoiceNumber] nvarchar(255) '$.InvoiceNumber',
	[Date] datetime '$.Date'
) AS [Invoices]

To extract nested fields, you define a field to store the nested JSON data within the WITH CLAUSE using the following syntax.

[FieldName] nvarchar(max) as JSON

You can then use the CROSS APPLY clause with an additional OPENJSON function that receives this field as input as per the following examples.

SELECT
	[Invoices].[DataFileID],
	[Invoices].[Type],
	[Invoices].[InvoiceNumber],
	[Invoices].[Date],
	[LineItems].[ItemCode],
	[LineItems].[Quantity],
	[LineItems].[LineAmount]
FROM OPENJSON(dbo.GetODataPages('url', 'username', 'password'), '$.value')
WITH
(
	[DataFileID] uniqueidentifier '$.DataFile.DataFileID', 
	[Type] nvarchar(20) '$.Type',
	[InvoiceNumber] nvarchar(255) '$.InvoiceNumber',
	[Date] datetime '$.Date',
	[LineItems] nvarchar(max) as JSON
) AS [Invoices]
CROSS APPLY OPENJSON([Invoices].[LineItems])
WITH
(
	[ItemCode] nvarchar(255) '$.ItemCode',
	[Quantity] float '$.Quantity',
	[LineAmount] float '$.LineAmount'
) AS [LineItems]

Understanding JSON Path Expressions

JSON Path is fairly easy to understand.

The dollar symbol ($) represents the root node.

You navigate within elements using the period symbol (.). For instance, the following syntax returns the the DataFileID of the DataFile field.

'$.DataFile.DataFileID'

You navigate within arrays via the square brackets ([ ]). For instance, the following syntax retrieves the first and second tracking category.

'$.Tracking[0].Option'
'$.Tracking[1].Option'

Constructing the URL to use with OdataLink CLR

The OData Feed url copied from within OdataLink represents the root service url which provides information on all endpoints and schemas. Most OData clients use it directly.

However, for it to work with the OdataLink CLR for SQL Server, you will need to append the actual name of the endpoint to the end of the url.

As an example, the following url provides access to sample data.

https://<odatafeed url>/

If you want to download to download data from the chart of accounts, you would change the url to include the Accounts endpoint.

https://<odatafeed url>/Accounts

In addition, you can append the odata $filter query parameter to the end of most url to include filter criterias. These must follow the OData specification.

For example, if you wanted to filter revenue accounts, you would use the following url.

https://<odatafeed url>/Accounts?$filter=Class eq 'REVENUE'

Function endpoints, on the other hand, accept comma parameters appended to the end of the url and in between parenthesis ( ).

For example, if you wanted to call the Profit and Loss xero function, you would use the following url.

https://<odatafeed url>/ProfitAndLoss(FromDate=2022-01-01,ToDate=2022-01-31)

Lastly, note that to use the above url within Transact-SQL, you will need to escape apostrophes (by using two apostrophes). You would invoke the function as follows.

dbo.GetODataPages('https://<odatafeed url>/Accounts?$filter=Class eq ''REVENUE''', 'username', 'password')

Download the Source Code

You can download the VB.net source code for the OdataLink CLR using the following link.

https://odatalink.com/downloads/sql-server/OdataLinkCLR/OdataLinkCLR-source-vb-net.zip