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>
Used as a source
When used as a source provider, the following settings are available for the XML Provider (Figure 2.1).
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 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)
Used as a destination
When used as a destination provider, the following settings are available for the XML Provider (Figure 3.1).
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
Which tables and columns?
When the XML provider is used as a source, the tables/columns depend on the input file that you provide.
When the XML provider is used as a destination, the tables/columns to be generated are defined during mapping.
The config-node
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)
Overriding provider settings at runtime
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>
Creating custom fields on import
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 |
Appendix A: Using XSL to transform your XML
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>