How to connect XML with Microsoft SharePoint lists

This "how-to-connect" documentation will explain the initial configuration of an example connection from Layer2 Cloud Connector synchronization history XML files to a SharePoint Online List in Office 365.

 

This guide presupposes that you have installed the Layer2 Cloud Connector and that you are familiar with its basic functionality. The Layer2 Cloud Connector User Documentation will provide you with all necessary information.

 

Contents

 

 

1. Create Connection

Create a new uni-directional connection as shown in the following picture.

Connection Setup

 

2. Create SharePoint List

Create a SharePoint list as the target for this synchronization. You can follow this example image for the column creation.

Target List

 

3. Setup Data Entities

 

3.1. Setup Source

Set up "Data Entity 1" as the source entity, select our XML data provider and fill out the configuration settings.

Source Setup

 

Connection String:

Directory=C:\ProgramData\Layer2 Cloud Connector\History;

 

Select Statement:

SELECT @startTime as Sync Start, @endTime as Sync End, @warningCount as Warnings, @recordsSuccessfullySynchronized as Successful Changes, @recordsUnchanged as Unchanged Records, @recordsSkippedIntentionally as Intentionally Skipped Records, @recordsSkippedDueToError as Unintentionally Skipped Records, abortingError/@message as Aborting Error FROM /history/entries/entry[not(@endTime = '0001-01-01T00:00:00.0000000')]

 

The select statement utilizies aliasing to provide auto-mapping-compatible column names. You can adjust those names based on your own SharePoint list configuration.

 

We filter out any currently running synchronizations with the XPath condition near the end of the select statement because SharePoint cannot handle those temporary end time values.

 

3.2. Test Source

Use the "Preview Data" action on the right action panel to make sure your histories are read correctly.

Source DataSource Data Preview

 

3.3. Setup Target

Set up "Data Entity 2" as the target entity, select our SharePoint provider, fill out the configuration settings and finally click on "Refresh Authentication Token" to log into your SharePoint.

Target Setup

 

Connection String:

Url=https://<MySharePointListUrl>/;Authentication=Microsoft_Modern;

 

Please note: This is a sample with placeholders to be replaced with your actual settings. Please refer to the product documentation for SharePoint provider setup, if this configuration does not work for you.

 

3.4. Test Target

Use the "Preview Data" action on the right action panel to make sure your empty SharePoint list is accessible and read correctly.

Target Data Preview

 

4. Configure Mapping

Navigate to the mapping configuration for this connection and wait for the auto-mapping to be calculated.

Auto Mapping

Our XML provider comes with a "FilePath" column that shows the location of scanned XML files. SharePoint lists have an internal column called "FilePath", so in this case, we have to manually adjust the mapping.

 

Disable auto-mapping and adjust the FilePath mapping entry to point to the SharePoint column that should contain the XML file path. Then verify the mapping to ensure it is configured correctly.

Manual Mapping

 

5. Run Synchronization

Successful Synchronization

Enjoy your Layer2 Cloud Connector synchronization statistics in SharePoint, where you can set up workflows for error reporting and all kinds of other follow-up steps.

Target Data

 

Search for more data integration & synchronization solutions

Icon of Contact us - Chat - Layer2 leading solutions

Questions?

Contact us directly to discuss your specific requirements, help you with purchasing, or with any other questions.

 

Icon for Layer2 Solutions Finder- Layer2 leading solutions

About the Layer2 Cloud Connector

Get more information about the Layer2 Cloud Connector on the product page.

 

 

Icon for Layer2 Solutions Finder- Layer2 leading solutions

Try it out for 15 days

Get a free trial of the Layer2 Cloud Connector after a short registration.