Two-way sync between third-party XML export and SharePoint Online

[conwayjh]

I am looking for a solution to connect a third-party application with SharePoint Online, making it possible to edit data in the application from either end and have it update to the other.

The third party application is tied to a Azure SQL Database but SharePoint is not hosted in Azure. The application stores data using proprietary methods so the only way to get the data is by exporting it as an XML file, for which I have the schema and am able to serialize into C# classes.

One requirement that I am dealing with is that we would like this data connection to inform the SharePoint metadata service and allow tagging documents based on certain column values.

For something like this I would have assumed a WCF service would be necessary to consume the XML and make it available in SharePoint, but I am not sure where we would host this as we do not host SharePoint on-premises.

I have been looking into solutions based around using Azure Functions or hosting a RESTful API, and have even looked into building a SharePoint-Hosted Add-In for the external content type, but I am not sure which route is best for the functionality I desire. Looking for advice on how to tackle this complicated request.

[#2]

Hi @conwayjh 

did you need to update your Term store and update managed metadata?
Is it correct the needs?

Imho, you can define an Azure AD Application  and trust SharePoint Online for reading and write Metadata, writing an azure function in C# and SharePoint CSOM (example). In this way, with an application id and a self-signed cert, you are working with an s2s authentication. The Azure function could be scheduled.

I suggest you use SharePointPnPCoreOnline library, with a lot of useful extension methods

You can also consider if you don't need high reliability or others skills, Microsoft Flow, there is SQL Server connector that could trigger your azure function. Actually, there is not REST API for manage Terms or TermSet creation.

Cheers,

Federico

[#3]

@Federico Porceddu 

I am pretty new to Azure, and the org already has an Domain controller server that handles AD. I am thinking I am going to have to update the term store and MM, yes, or add an external content type to handle the data once it is serialized from XML, because the data needs to map to SharePoint as well as a custom ASP.NET web application (also hosted in Azure).

One big problem I see is that we cannot work directly with the database of the application producing the XML - we can only export an XML file using the app's interface or a command line tool, which I could program to happen on a schedule but not manually as a trigger of any kind (that I can tell).

One last thing - I had trouble finding the documentation for that library. Is this it?