SharePoint: How to sync external XML data with native lists?

The Business Data List Connector for SharePoint connects almost any on-premise or cloud-based data source, e.g. ODBC, OLEDB, OData, Microsoft .NET based providers, Files (Excel, XML, CSV), SQL databases like SQL Server, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Navision, SAP and many more directly to native SharePoint lists - in just minutes without any programming. This FAQ gives a step-by-step intro about connecting XML data.​​​​​​

Integrating external XML data sources (e.g. from LOB applications) from file system or web requests with native SharePoint lists (e.g. news, contacts, tasks or custom lists) can close many gaps using Microsoft SharePoint. Please note: It's not only about displaying external data - it's about full integration with views, search, lookups, change notifications, workflows, enterprise metadata etc. This FAQ gives a step-by-step example how to do this.

Prepare your external XML data source to integrate with SharePoint

Let's take a sample XML, the well-known CD catalog. Think of it like an export from your LOB application, e.g. ERP, CRM or something like this.

Figure 1. Example of a XML-based document to connect with a native SharePoint list.

Please note:

• Almost any LOB system should be able to expose data in a format like this.
• XML files are supported as well as web requests (per URL).
• You can query / filter data subsets later on using XPath.

Setup your XML to SharerPoint connection

• Create your list in SharePoint, e.g. a custom list named XML. Tipp: Use an appropriate list type, e.g. contacts for address-based information etc.
• Go to List Settings > General Settings > Connect to external data source. If you don't have this link, please activate the Business Data List Connector for the current site collection.
• Select the Layer2 Data Provider for XML as connection provider.
• Enter the following settings as Connection String: e.g. URL=https://www.w3schools.com/xml/cd_catalog.xml
  alternatively you can use XML files, e.g. URL=C:\myfile.xml.
  Validate the connection string.
• Enter the following XPath settings as Select Statement:
  e.g. select * from /CATALOG/CD[COUNTRY="USA"]
  Validate the select statement. Preview your data. Take a look at the content: Are there unique columns to use as primary key? In this case TITLE should be unique.
• Enter the Primary Key, "TITLE" in this sample. The primary key allows to BDLC to update items later on, in case your source data changes. Validate your primary key.
• Note that column and node names are case-sensitive. If you aren't able to get the query or the primary key working, check that you set your case and spelling correctly.
• Click on "Add Columns" to create the list structure for hosting the external data. Please check mapping. Mapping is done automatically by internal SharePoint column names and data source field names, but you can also manually map columns. You can change column titles and column types (e.g. currency) later on.
• Click update for first insert. Please note that this could take a while, depending on data amount and configuration. If done, click update again. It should be much faster, and there should be no changes (based on primary key given).
• You can now enter some final options about background update, e.g. scheduling, etc., if required.
• Don't forget to save your connection with "OK".

Figure 2. How to setup a connection to a XML-based data source with the Business Data List Connector.

Please note that you can't enable write-back. This XML Provider is read-only.

See your XML data in the SharePoint list

​Now take a look at your list. All items are in there, after running the connection.

Figure 3. The external data is hosted in a native SharePoint list.

Please note:

• Selected items are transferred only, e.g. only items with country = "USA" in this sample. Alternatively you can retrieve all data and use views in SharePoint.
• Changed items are transferred only on update. That means, you can setup change notifications per-email or workflows in the cloud without any restrictions.
• You can add additional "SharePoint only" columns, e.g. for attachments, managed metadata, or other social content. These columns will be kept while mapped items are updated.
• Users have no direct access to your external data, no license is required in case of CALs for your source system.
• You can do anything you can generally do with a native SharePoint list - without any restrictions (e.g. search).
• Don't forget to setup the background update, to keep data current in connection settings, e.g. with scheduled updates every hour or daily. Alternatively you can update on demand, by ribbon button, URL or API.

Next Steps: Register and Download Free Shareware Edition

​For the complete Layer2 Data Provider for XML specifications, see here. You can download a free shareware edition of the Layer2 Business Data List Connector after registration here.

READY TO GO NEXT STEPS?