Business Integration Solutions documentation

About: 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 BIS pipeline can read and process.

Concept

Microsoft Dynamics BC 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, and Node Policies.

General Setup

Field	Description
Target Document Type	The document type of the document representing the translated XML.
Target Document No	The document number of the document representing the translated XML.
Json Translator	The translator to use. Translators define in enum BISJsonTranslator. Currently BIS and BIS Legacy exist. BIS represents the latest version and is the default for new activities of this type.
Process Empty Document	Indicates whether the connection continues when no nodes were transformed.

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

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.

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

Examples:

AttributeBlock: Attributes, AttributePrefix: ''

Input:

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

Output:

<Item No="1000">
    <Description>Bicycle</Description>
</Item>

AttributeBlock: '', AttributePrefix: _

Input:

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

Output:

<Item No="1000">
    <Description>Bicycle</Description>
</Item>

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 <Description>-NULL-</Description>

NullValue: -NULL- → output includes <Description />

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 <TEXT>1000</TEXT> nodes nested

TextNodeName: TEXT → unwraps to <No>1000</No>

Invalid character replacement

Accepts a text string.

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

Example:

Input:

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

InvalidCharReplacement: _ → <Item_Number>, <Item_Description>

InvalidCharReplacement: _Err → <Item_ErrNumber>, <Item_ErrDescription>

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:

<root>
  <no/><name/>
  <top>
    <no/><name/>
    <middle><no/><name/></middle>
  </top>
</root>

[{"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

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:

<Item><No>1000</No><Comments>Comment1</Comments><Comments>Comment2</Comments></Item>

WrapArray checked:

<Item><No>1000</No><Comments><Comment>Comment1</Comment><Comment>Comment2</Comment></Comments></Item>

Skip Node

Skips certain JSON nodes from translating to XML.

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 <document>. 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 <document> wraps the existing document:

Input: {"Item":{"No":"1000"}} → Output: <document><Item><No>1000</No></Item></document>

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: <document><Item><No>1000</No></Item></document>

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 <document> as root node. The element name Item retrieves from the root node of the external document.

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

Example 3. JSON object with single field matching the document root node <Item>: 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 <document> adds.