XML Provider

The XML Provider lets you import and export data wrapped in XML.

The XML structure is an approximation of the database structure, and is used for both export and import jobs.

The outer <tables> node contains a <config> section with custom field definitions and other settings, followed by your <tables> and their <item>s and <column>s.

If the attribute “isNull” is set to true, the input will be treated as a Null value. Otherwise, an empty node will be treated as an empty string.

Here is an example of the XML structure generated by the XML Provider:

<tables> <config> <customField name="ItemCode" systemName="ItemCode" templateName="ItemCode" typeId="1" /> <customField name="TaxCode" systemName="TaxCode" templateName="TaxCode" typeId="15" /> <customField name="MyFancyField" systemName="myfancyfield" templateName="myfancyfield" typeId="11" /> </config> <table tableName="EcomProducts"> <item table="EcomProducts"> <column columnName="ProductNumber"><![CDATA[1]]></column> <column columnName="ProductName"><![CDATA[Dimmer ]]></column> <column columnName="ProductShortDescription"><![CDATA[<p>Dimmer - the Original!</p>]]></column> <column columnName="ProductLongDescription"><![CDATA[<p>Who can honestly say that a beautiful dimmer leaves them untouched? Gaze upon this mechanical wonder and ponder. Ponder the fate of humanity, the insufferable beauty of winter, and the brightness of a single, pure thought.</p>]]></column> <column columnName="ProductLink2"><![CDATA[]]></column> <column columnName="ProductStock"><![CDATA[972]]></column> <column columnName="ProductWeight"><![CDATA[1]]></column> <column columnName="ProductVolume"><![CDATA[1]]></column> <column columnName="ProductVatGrpId"><![CDATA[]]></column> <column columnName="ProductActive"><![CDATA[True]]></column> <column columnName="ProductType"><![CDATA[0]]></column> <column columnName="myfancyfield" isNull="true" /> </item> <item table="EcomProducts"> <column columnName="ProductNumber"><![CDATA[5]]></column> <column columnName="ProductName"><![CDATA[Dippedut]]></column> <column columnName="ProductShortDescription"><![CDATA[<p>Dippedut - Danish for Progress!</p>]]></column> <column columnName="ProductLongDescription"><![CDATA[<p>A Dippedut will help you in hundreds of non-specific ways, ways which will make you a more beautiful, radiant individual. You may even lose weight!</p>]]></column> <column columnName="ProductLink2"><![CDATA[]]></column> <column columnName="ProductStock"><![CDATA[995]]></column> <column columnName="ProductWeight"><![CDATA[0,2]]></column> <column columnName="ProductVolume"><![CDATA[1]]></column> <column columnName="ProductVatGrpId"><![CDATA[]]></column> <column columnName="ProductActive"><![CDATA[True]]></column> <column columnName="ProductType"><![CDATA[0]]></column> <column columnName="myfancyfield" isNull="true" /> </item> </table> </tables>

When used as a source provider, the following settings are available for the XML Provider (Figure 2.1).

Figure 2.1 The XML Provider may be used as a source provider, in which case these options are available

You must:

  • Select a source folder or source file (source folder will be ignored in favor of source file if both are selected). You can choose to automatically archive the source files to an "archive" subfolder, or to delete the source file after it’s been imported. If folder is used one file = 1 table.
  • Optionally, select an XSL file which transforms the XML in the input file into the format required by the XML provider
  • Select a source decimal separator – choose between comma (,), dot (.), using the system culture (of the IIS), or detect automatically (based on last non-numeric character in numbers)

Importing data from multiple XML files

The following providers can handle multiple XML files in the input data, provided that each file is limited to one table:

  • SQL provider
  • Dynamicweb provider
  • Ecom provider
  • User provider

For the EcomProvider it is also important to note that you should not use several files as a source for the EcomProductsRelated table, as each table will override the previous import - not append to it.

When used as a destination provider, the following settings are available for the XML Provider (Figure 3.1).

Figure 3.1 When used as a destination provider, these are the options available to you

You can:

  • Optionally select an XSL file for transforming the XML format exported from Dynamicweb into another format
  • Specify a name for the destination file which will be generated
  • Select a destination folder to create the file in
  • Optionally select a destination encoding – default is UTF8 – and a number format culture (None = IIS culture, other options are from your Ecommerce languages)

Additionally, you can:

  • Check include timestamp in filename to, well, include a timestamp in the filename of generated XML files
  • Check Skip troublesome rows to skip rows with characters not allowed in XML (the activity fails if not)
  • Check Export Product Field definitions to apply the structural definition of product fields to a node, located at the top of the output XML

When used as a source, the tables/columns depend on the input file that you provide. Missing columns in the input maps to NULL.

When used as a destination, the tables/columns to be generated are defined during mapping

Adding a <config>-node at the top of your XML file lets you do two things:

  • Override provider settings at runtime
  • Create custom fields (during import)

Using the <config>-node, you can override the source and destination provider settings at runtime, as in Figure 6.1). The field names must match property names in the providers. This is case sensitive. 

<config> <source> <setting field ="SkipTroublesomeRows"> False </setting> </source> <destination> <setting field ="DeactivateMissingProducts"> True </setting> </destination> </config>

To create custom fields during import, you simply add a custom field section to the <config>-node:

<config> <productCustomField name="ProductCustomDescription" systemName="ProductCustomDescription" templateName="ProductCustomDescription" typeId="1"></productCustomField> <orderCustomField name="ProductCustomDescription" systemName="ProductCustomDescription" templateName="ProductCustomDescription" typeId="1"></orderCustomField> <categoryField id="CategoryField1" templateTag="CategoryField1Tag" label="Category Field1" categoryId="MyCategory" typeId="1" /> <categoryField id="CategoryField2" templateTag="agCategoryField2Tag" label="Category Field2" categoryId="MyCategory" typeId="1" /> </config>

The possible typeIds for product fields and order fields are:

TypeID

Field type

1

Text (255)

2

Long text

3

Check box

4

Date

5

Date/Time

6

Integer

7

Decimal

8

Link

9

File

10

Text (100)

11

Text (50)

12

Text (20)

13

Text (5)

14

Editor

15

List box

 

Custom OrderLine fields and user fields can be created in a similar manner:

<config> <orderLineCustomField name="ProductCustomDescription" systemName="ProductCustomDescription" length="100"> </orderLineCustomField> <userCustomField name="ProductCustomDescription" systemName="ProductCustomDescription" Type="0"></userCustomField> </config>

The possible typeIds for user fields are:

TypeID

Field type

0

Text

1

Long text

2

Rich editor

3

Number

4

Date

5

DateTime

6

File

7

PageLink

8

Boolean

9

Dropdown

10

Radio button group

If you need to import data from an XML file with a schema that does not match the format required by the XML Provider, or if you need the data you export to be formatted in a way consisted with another standard, you will have to create XSL files which transform the XML into the correct form. You can read more about XSL at w3: https://www.w3.org/Style/XSL/

During both source and destination provider setup, you can select an XSL file to transform your input/output XML.

Consider this example product data in a non-Dynamicweb XML format:

<Export> <Items> <Item ItemNo="PRODD1" ShopID="SHOP1" LanguageCode="LANG1" VariantCode="" Description="Red shoe" Description2="" Unit="Pair" UnitPrice="165" Inventory="2" FeaturedProduct="no" PrivateProduct="yes" ExtendedDescription=""> <Translation Language="LANG1" VariantCode="" Description="Red shoe" Description2=""/> <Translation Language="LANG2" VariantCode="" Description="roten Schuh" Description2=""/> <Category No="123"/> </Item> <Item ItemNo="PRODD2" ShopID="SHOP1" LanguageCode="LANG1" VariantCode="" Description="Blue shoe" Description2="" Unit="Pair" UnitPrice="165" Inventory="4" FeaturedProduct="no" PrivateProduct="yes" ExtendedDescription=""> <Translation Language="LANG1" VariantCode="" Description="Blue shoe" Description2=""/> <Category No="123"/> </Item> <Item ItemNo="PRODD3" ShopID="SHOP1" LanguageCode="LANG1" VariantCode="" Description="Green shoe" Description2="" Unit="Pair" UnitPrice="45" Inventory="40" FeaturedProduct="no" PrivateProduct="yes" ExtendedDescription="A beautiful green shoe."> <Translation Language="LANG1" VariantCode="" Description="Green shoe" Description2=""/> <Category No="444"/> </Item> </Items> </Export>

The above data – three products, one in two languages – makes it necessary to create four rows for the product table, as each language version is a separate row in Dynamicweb.

In order to achieve this, we loop through the items, and for each item we loop through the translations, adding a row for each one. The XSL needed to achieve this is shown below:

<?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output cdata-section-elements="column"/> <xsl:template match="/"> <tables> <table tableName="EcomProducts"> <xsl:for-each select="Export/Items/Item"> <xsl:for-each select="Translation"> <item table="EcomProducts"> <column columnName="ProductID"> <xsl:value-of select="../@ItemNo"/>@@<xsl:value-of select="../@ShopID"/> </column> <column columnName="ProductNumber"> <xsl:value-of select="../@ItemNo"/> </column> <column columnName="ProductLanguageID"> <xsl:value-of select="@Language"/> </column> <column columnName="ProductVariantID"> <xsl:value-of select="@VariantCode"/> </column> <column columnName="ProductName"> <xsl:value-of select="@Description"/> </column> <column columnName="ProductShortDescription"> <xsl:value-of select="@Description2"/> </column> <column columnName="ProductLongDescription"> <xsl:value-of select="../@ExtendedDescription"/> </column> <column columnName="ProductPrice"> <xsl:value-of select="../@UnitPrice"/> </column> <column columnName="ProductStock"> <xsl:value-of select="../@Inventory"/> </column> <xsl:if test="../@PrivateProduct='no'"> <column columnName="PRIVATE">false</column> </xsl:if> <xsl:if test="../@PrivateProduct='yes'"> <column columnName="PRIVATE">true</column> </xsl:if> <xsl:if test="../@FeaturedProduct='no'"> <column columnName="FeaturedProduct">false</column> </xsl:if> <xsl:if test="../@FeaturedProduct='yes'"> <column columnName="FeaturedProduct">true</column> </xsl:if> <column columnName="ProductActive">true</column> <xsl:variable name="currentLanguage" select="@Language"/> </item> </xsl:for-each> </xsl:for-each> </table> </tables> </xsl:template> </xsl:stylesheet>

The result of this transformation is:

<?xml version="1.0"?> <tables> <table tableName="EcomProducts"> <item table="EcomProducts"> <column columnName="ProductID"> <![CDATA[PRODD1@@SHOP1]]> </column> <column columnName="ProductNumber"> <![CDATA[PRODD1]]> </column> <column columnName="ProductLanguageID"> <![CDATA[LANG1]]> </column> <column columnName="ProductVariantID"/> <column columnName="ProductName"> <![CDATA[Red shoe]]> </column> <column columnName="ProductShortDescription"/> <column columnName="ProductLongDescription"/> <column columnName="ProductPrice"> <![CDATA[165]]> </column> <column columnName="ProductStock"> <![CDATA[2]]> </column> <column columnName="PRIVATE"> <![CDATA[true]]> </column> <column columnName="FeaturedProduct"> <![CDATA[false]]> </column> <column columnName="ProductActive"> <![CDATA[true]]> </column> </item> <item table="EcomProducts"> <column columnName="ProductID"> <![CDATA[PRODD1@@SHOP1]]> </column> <column columnName="ProductNumber"> <![CDATA[PRODD1]]> </column> <column columnName="ProductLanguageID"> <![CDATA[LANG2]]> </column> <column columnName="ProductVariantID"/> <column columnName="ProductName"> <![CDATA[roten Schuh]]> </column> <column columnName="ProductShortDescription"/> <column columnName="ProductLongDescription"/> <column columnName="ProductPrice"> <![CDATA[165]]> </column> <column columnName="ProductStock"> <![CDATA[2]]> </column> <column columnName="PRIVATE"> <![CDATA[true]]> </column> <column columnName="FeaturedProduct"> <![CDATA[false]]> </column> <column columnName="ProductActive"> <![CDATA[true]]> </column> </item> <item table="EcomProducts"> <column columnName="ProductID"> <![CDATA[PRODD2@@SHOP1]]> </column> <column columnName="ProductNumber"> <![CDATA[PRODD2]]> </column> <column columnName="ProductLanguageID"> <![CDATA[LANG1]]> </column> <column columnName="ProductVariantID"/> <column columnName="ProductName"> <![CDATA[Blue shoe]]> </column> <column columnName="ProductShortDescription"/> <column columnName="ProductLongDescription"/> <column columnName="ProductPrice"> <![CDATA[165]]> </column> <column columnName="ProductStock"> <![CDATA[4]]> </column> <column columnName="PRIVATE"> <![CDATA[true]]> </column> <column columnName="FeaturedProduct"> <![CDATA[false]]> </column> <column columnName="ProductActive"> <![CDATA[true]]> </column> </item> <item table="EcomProducts"> <column columnName="ProductID"> <![CDATA[PRODD3@@SHOP1]]> </column> <column columnName="ProductNumber"> <![CDATA[PRODD3]]> </column> <column columnName="ProductLanguageID"> <![CDATA[LANG1]]> </column> <column columnName="ProductVariantID"/> <column columnName="ProductName"> <![CDATA[Green shoe]]> </column> <column columnName="ProductShortDescription"/> <column columnName="ProductLongDescription"> <![CDATA[A beautiful green shoe.]]> </column> <column columnName="ProductPrice"> <![CDATA[45]]> </column> <column columnName="ProductStock"> <![CDATA[40]]> </column> <column columnName="PRIVATE"> <![CDATA[true]]> </column> <column columnName="FeaturedProduct"> <![CDATA[false]]> </column> <column columnName="ProductActive"> <![CDATA[true]]> </column> </item> </table> </tables>