JSONTOXMLTRANSLATOR activity

In Business Integration Solutions, use the JSONTOXMLTRANSLATOR activity to translate a JSON document into an XML document.

Usage

Use this activity to translate a JSON document into an XML document that the Business Integration Solutions pipeline can read and process.

Concept

Microsoft Dynamics 365 Business Central cannot interpret data stored in JSON (JavaScript Object Notation) format. If your company subscribes to data in JSON format, use the JSON to XML Translator activity in your import pipeline. This activity converts the incoming data into a readable XML format based on a predefined internal document.

To use this activity, define an internal or external document on which the incoming data translates.

Setup

Setup for this activity splits into three sections: General setup, Policies setup, and Node policies setup.

General setup

Note

The BIS Legacy translator is the old translator. It does not use all the policies and options shown below. Switch to the BIS translator.

Policies setup

Policies represent settings for the translation.

The following policies are available for JSON to XML: AttributeBlock, AttributePrefix, NullValue, TextNodeName, InvalidCharReplacement, and MatchStart.

AttributeBlock and AttributePrefix

AttributeBlock and AttributePrefix accept a text value or an empty string.

If AttributeBlock has a value, a field with the configured name retrieves attributes of the target element.

If AttributePrefix has a value, fields with that prefix search in the JSON object to get the list of attributes.

Note

If both AttributeBlock and AttributePrefix are empty, no attributes add to elements.

Examples:

AttributeBlock: Attributes, AttributePrefix: ''

Input:

{
  "Item": {
    "Attributes": { "No": "1000" },
    "Description": "Bicycle"
  }
}

Output:

    Bicycle

AttributeBlock: '', AttributePrefix: _

Input:

{
  "Item": {
    "_No": "1000",
    "Description": "Bicycle"
  }
}

Output:

    Bicycle

NullValue

Accepts a text string.

If a JSON value is null or its text value matches the configured null value, it does not add that value to the element.

Examples:

Input:

{ "Item": { "No": "1000", "Description": "-NULL-" } }

NullValue: '' → output includes -NULL-

NullValue: -NULL- → output includes

TextNodeName

Accepts a text string.

If a JSON field has the name configured in TextNodeName, its value unwraps and converts to an XML text node.

Example:

Input:

{ "Item": { "No": { "TEXT": "1000" }, "Description": { "TEXT": "Bicycle" } } }

TextNodeName: '' → keeps 1000 nodes nested

TextNodeName: TEXT → unwraps to 1000

Invalid character replacement

Accepts a text string.

If a JSON key cannot convert to a valid XML node, Anywhere Mobility Solutions invalid characters replace with this text.

Example:

Input:

{ "Item": { "Item Number": "1000", "Item Description": "Bicycle" } }

InvalidCharReplacement: _ → ,

InvalidCharReplacement: _Err → ,

MatchStart

The MatchStart policy identifies with which node the JSON object starts to match. This lets the JSON start on a deeper level in the XML. By default the JSON matches with the root node. See Getting the root node for internal documents and Getting the root node for external documents.

If MatchStart is on a level higher than zero, it tries to match with the first node on that level.

Example, document definition and input JSON:

[{"no":"1","name":"first of his name"},{"no":"2"},{"no":"3","name":"third of her name"}]

Match level 0 (default) (document structure changes because the JSON starts with an array. Match level 1) empty nodes generate in parent nodes but child nodes do not. Document structure stays intact. Match level 2, matches from the middle level.

Node policies setup

Node policies tie to specific nodes in the document. Only block nodes can have policies at this time.

WrapArray

Wraps arrays into additional nodes.

Example:

Input:

{ "Item": { "No": "1000", "Comments": ["Comment1","Comment2"] } }

WrapArray unchecked on Item/Comments/Comment:

1000Comment1Comment2

WrapArray checked:

1000Comment1Comment2

Skip Node

Skips certain JSON nodes from translating to XML.

Note

The root node of a document cannot have this policy. A document block with this policy must define as non-mandatory, and this rule applies to all its children.

Example, with Item\FirstChild having SkipNode checked, the output excludes the FirstChild block and all its children.

Additional information

Handling of CData

If the WrapCData option is checked for a document line, the value retrieved from JSON wraps in an XML CData node before adding to the XML element.

Getting the root node for target internal documents

Internal documents must always have a root node called . This always adds when translating JSON to XML as the root node, if the target document type is Internal. If the JSON object to translate starts with a single field called document, this assumes as the root node and skips in further processing.

Example 1, the wraps the existing document:

Input: {"Item":{"No":"1000"}} → Output: 1000

Example 2, same output even with an additional JSON level, because the first level has a single field called document:

Input: {"document":{"Item":{"No":"1000"}}} → Output: 1000

Getting the root node for target external documents

External documents must have a single root node but the name may vary. The examples below assume the root node of the external document is Item.

Example 1. JSON array input: creates as root node. The element name Item retrieves from the root node of the external document.

Example 2. JSON object with several fields: creates as root node.

Example 3. JSON object with single field matching the document root node : the first field assumes as the root.

Example 4. JSON object with single field matching document: the first field assumes as the root.

Example 5. JSON object with single field not matching document or the external document root node: a wrapping adds.