DataBridge (nested) XML File import

Overview

With DataBridge it is possible to import XML files into SuperOffice CRM.
If you want to know what other types of files are supported, please visit our System Requirements page.

As of release 104 - 2 Oktober 2020, the format of XML source files supported by DataBridge is much more flexible, with the possibility to include properties in nested tags and to import nested collections.

How to

In the Configure Profile page in your profile, you can upload your XML file.

Setting Main entity's in section “General” is a drop down list that lets the user choose which XML tag represents an entity to import.

Under the Advanced settings, you can set what text encoding is used in your file. A preview of the data is shown at the bottom of the screen.

Consider the following example:

XML Code

<?xml version="1.0" encoding="UTF-8"?>
<companies>
  <company>
    <name>Company 1</name>
    <contacts>
      <contact>
        <firstname>First name 1</firstname>
        <lastname>Last name 1</lastname>
        <phones>
          <phone>+123</phone>
          <phone>+456</phone>
        </phones>
      </contact>
      <contact>
        <firstname>First name 2</firstname>
        <lastname>Last name 2</lastname>
      </contact>
    </contacts>
    <invoices>
      <invoice>
        <date>01/02/03</date>
      </invoice>
      <invoice>
        <date>04/05/06</date>
      </invoice>
    </invoices>
  </company>
</companies>

The options would be:

companies
companies/company ← select this one if “company“ is the entity to import
companies/company/contacts
companies/company/contacts/contact ← select this one if “contact“ is the entity to import

If the file contains only one entity/record to import instead of a collection, then the root tag should be selected. represented by the root tag

Columns extracted from the XML file depend on the selected option. See in the table below how the header of the preview table would look like for each option and the number of rows obtained.

companies	company/name	company/contacts/contact/firstname	company/contacts/contact/lastname	1
companies/company	name	contacts/firstname	contacts/lastname	2
companies/company/contacts	contact/firstname	contact/lastname		2
companies/company/contacts/contact	firstname	lastname		3

Backward compatibility

If no option is selected, then by default the first tag found in the root tag is used (“companies/company“ in the example), which is the tag that was automatically used prior to release 104.

Profiles created before this release don’t require any change and will be processed just like new profiles with no “Main entity“ selected.

The name of the columns used in these profiles are unchanged. New columns with a “/“ in the name might appear in the preview if the XML file contains nested tags that used to be ignored, but it doesn’t impact column mapping since columns order doesn’t matter.

Inner entities

Setting Inner entities in section “General“ is a drop down list to select one single nested collection of entities (if any) to import along the main entity.

In the example above, with “companies/company“ selected as Main entity user can choose between “-None-“ and “contacts“. If the latter is selected, then the file will be flattened, meaning 3 rows will be shown in the preview instead of 2, one for each contact. The direct properties of the main entity (here the content of column “name”) will be repeated for each inner entity.

Only the tags which are a direct child of the Main entity tag can be selected to import inner entities, and only one of these tags can be selected.

Consider the above mentioned xml example:

Selected Main entity	Inner entities options
companies/company	contacts invoices
companies/company/contacts/contact	phones

For all the inner entities that are not selected, only the first row is included in the data that can be exported. Also note that for technical reasons the columns that belong to the inner entities appear as the last columns. Here is what the preview for the example above would look like depending on each combination of options:

Main entity = companies/company, Inner entities = contacts

name	invoices/invoice/date	contacts/contact/firstname	contacts/contact/lastname	contacts/contact/phones/phone
Company 1	01/02/03	First name 1	Last name 1	+123
Company 1	01/02/03	First name 2	Last name 2

Main entity = companies/company, Inner entities = invoices

name	contacts/contact/firstname	contacts/contact/lastname	contacts/contact/phones/phone	invoices/invoice/date
Company 1	First name 1	Last name 1	+123	01/02/03
Company 1	First name 1	Last name 1	+123	04/05/06

Main entity = companies/company/contacts/contact, Inner entities = phones

firstname	lastname	phones/phone
First name 1	Last name 1	+123
First name 1	Last name 1	+456
First name 2	Last name 2

Backward compatibility

No Inner entity option is selected by default. Existing profiles with an XML file that contains nested collections that used to be ignored will get extra columns in the preview, which doesn’t impact column mapping.

The rules for a successful XML import are:

Rule 1: The file must have a valid XML format (including DTD (Document Type Definition)).
XML validity can be checked here for instance : https://www.xmlvalidation.com/

Rule 2: The records to be imported can start at any element that contains elements itself.

Rule 3: Any name can be used for the elements, but the records must have the same name, with the same case.

Rule 4: Properties are retrieved from the text content of the innermost element. Attributes are ignored as well as text content if the element also contains nested elements.

<companies>
<company id=”ID”>
<name>NAME</name>
<address>
ADDRESS LINE
<zipcode>ZIP</zipcode>
<city>CITY</city>
</address>
</company>
</companies>

Rule 5: All the properties that need to be imported must be present, even empty, in the first record of the file.

Rule 6: Each property needs to appear only once per record. Duplicates will be ignored.

Rule 7: Elements nested directly in the selected record element can be selected as a collection of inner entities.

Rule 8: Elements that contain at least two nested elements with the same name can be selected as inner entity. Only the first record of the sample file needs to follow this rule, actual files to process can contain only one entry in the selected collection of inner entities.

Rule 9: Elements that contain nested elements with different names or that directly contain text cannot be selected as a collection of inner entities.

<companies>
<company>
<contacts> 
CONTACTS
<contact>
<firstname>…</firstname>
<lastname>…</lastname>
</contact>
<contact>
<firstname>…</firstname>
<lastname>…</lastname>
</contact>
</contacts>
<phones> 
<phone>…</phone>
<phone>…</phone>
<mobilephone>…</mobilephone>
</phones>
</company>
</companies>

Rule 10: Nested elements of an element selected as a collection of inner entities can contain nested entities on multiple levels or directly contain text.

Rule 11: The collection of inner entities for the first record must contain at least one inner entity in the files to process.