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

Using this activity you can translate a JSON document into an XML document, which can be easily read and processed by the BIS pipeline.

Concept

Microsoft Dynamics BC cannot interpret data stored in JSON (JavaScript Object Notation) format. But in case your company is subscribed to data stored in JSON format then you must use the JSON to XML Translator activity in your import pipeline. This activity helps you to convert the incoming data into a more readable XML format, based on a predefined internal document.

To be able to use this activity, you must define an internal/external document based on which the incoming data will be translated.

Setup

Setup for this activity is split into 3 sections.

• General setup
• Policies
• Node Policies

General Setup

Field	Description
Target Document Type	The document type of the document representing the translated XML.
Target Document No	The document no of the document representing the translated XML.
Json Translator	The translator that should be used to handle the translator. Translators are defined in enum BISJsonTranslator. Currently, BIS and BIS Legacy exist as translators. BIS represents the latest version of the translator and is the default when creating new activities of this type.
Process Empty Document	Indicates if the connection should continue when no nodes were transformed

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

Policies

The policies represent settings for the translation.

The following policies are available for JSON to XML.

• AttributeBlock
• AttributePrefix
• NullValue
• TextNodeName
• InvalidCharReplacement
• MatchStart

AttributeBlock and AttributePrefix

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

If AttributeBlock has a value, an field with the configured name is retrieved to get attributes of the target element.

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

If both AttributeBlock and AttributePrefix do not have values, no attributes are added to elements as they cannot be identified.

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 null value configured, it will not add that value to the element.

Examples

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

NullValue: ''

<Item>
	<No>1000</No>
	<Description>-NULL-</Description>
</Item>

NullValue: -NULL-

<Item>
	<No>1000</No>
	<Description />
</Item>

TextNodeName

Accepts a text string.

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

Examples

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

TextNodeName: ''

<Item>
	<No>
		<TEXT>1000</TEXT>
	</No>
	<Description>
		<TEXT>Bicycle</TEXT>
	</Description>
</Item>

TextNodeName: TEXT

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

Invalid character replacement

Accepts a text string.

If a JSON key cannot be converted to a valid XML node, any invalid characters are replaced with this text

Examples

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

InvalidCharReplacement: _

<Item>
	<Item_Number>1000</No>
	<Item_Description>Bicycle</Description>
</Item>

InvalidCharReplacement: _Err

<Item>
	<Item_ErrNumber>1000</Item_ErrNumber>
	<Item_ErrDescription>Bicycle</Item_ErrDescription>
</Item>

Node Policies

The node policies represent policies tied to specific nodes in the document. Only block nodes can have policies at this time.

WrapArray

This node policy is used to wrap arrays into additional nodes.

Examples

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

Item/Comments/Comment has WrapArray unchecked

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

Item/Comments/Comment has WrapArray checked

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

Skip Node

This node policy is used to skip certain json nodes from begin traslated to XML.

The root node of a document isn't allowed to have this policy set.

A document block marked with this policy must be defined in the document definition as a non-mandatory line. This rule applies for all its children.

Examples

{
  "Item": {
    "No": "1000",
    "Comments": [
      "Comment1",
      "Comment2"
    ],
    "FirstChild": {
      "Code": "1001",
      "Description": "First child description",
      "SecondChild": {
        "Code": "1002",
        "Description": "Second child description"
      }
    }
  }
}

No blocks have SkipNode checked

<Item>
  <No>1000</No>
  <Comments>
    <Comment>Comment1</Comment>
    <Comment>Comment2</Comment>
  </Comments>
  <FirstChild>
    <Code>1001</Code>
    <Description>First child description</Description>
    <SecondChild>
      <Code>1002</Code>
      <Description>Second child description</Description>
    </SecondChild>
  </FirstChild>
</Item>

Item\FirstChild\SecondChild has SkipNode checked

<Item>
  <No>1000</No>
  <Comments>
    <Comment>Comment1</Comment>
    <Comment>Comment2</Comment>
  </Comments>
  <FirstChild>
    <Code>1001</Code>
    <Description>First child description</Description>
  </FirstChild>
</Item>

Item\FirstChild has SkipNode checked

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

Item\Comments has SkipNode checked

<Item>
  <No>1000</No>
  <FirstChild>
    <Code>1001</Code>
    <Description>First child description</Description>
    <SecondChild>
      <Code>1002</Code>
      <Description>Second child description</Description>
    </SecondChild>
  </FirstChild>
</Item>

MatchStart

The matchstart policy identifies with which node the json object will start to match. This allows for the json to 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 the MatchStart is on a level higher than 0, it will try to match with the first node on that level.

Examples

The input json and the document are the same for all examples.
The Document Definition:

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

The input Json

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

Output with Matchlevel 0 (default)

The document structure changes, because the json starts with an array

<document>
  <root>
    <no>1</no>
    <name>first of his name</name>
  </root>
  <root>
    <no>2</no>    
  </root>
  <root>
    <no>3</no>
    <name>third of her name</name>
  </root>
</document>

Output with Matchlevel 1

Empty nodes in parent node(s) are generated, but childnodes aren't. The document structure isn't modified.

<root>
  <no/>
  <name/>
  <top>
    <no>1</no>
    <name>first of his name</name>
  </top>
  <top>
    <no>2</no>    
  </root>
  <top>
    <no>3</no>
    <name>third of her name</name>
  </top>
</document>

Output with Matchlevel 2

<root>
  <no/>
  <name/>
  <top>
    <no/>
    <name />
    <middle>
      <no>1</no>
      <name>first of his name</name>
    </middle>
    <middle>
      <no>2</no>
    </middle>
    <middle>
      <no>3</no>
      <name>third of her name</name>
    </middle>
  </top>
</document>

Additional Information

Handling of CData

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

Getting the root node for target internal documents

Internal documents must always have a root node called <document>. For this reason, this is always added when translating JSON to XML as the root node, if the target document type is Internal. If the JSON object to be translated starts with a single field called document, as shown in example 2 below, this is assumed as the root node and is skipped in further processing.

Example 1

In this case, the <document> wraps the existing document

Input

{
  "Item": {
    "No": "1000"
  }
}

Output

<document>
  <Item>
    <No>1000</No>
  </Item>
</document>

Example 2

In this case, we see the same output. Even though the JSON object has an addition level, since the first level has a single field called document, it is stripped and replaced by the root node.

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. In the examples below, it is assumed that the root node of the external document is Item.

Example 1 - JSON array input

If the JSON input is a JSON array, <document> is created as a root node. The element name Item is retrieved from the root node of the external document. Input

[
  {
    "No": "1000"
  },
  {
    "No": "1001"
  }
]

Output

<document>
  <Item>
    <No>1000</No>
  </Item>
  <Item>
    <No>1001</No>
  </Item>
</document>

Example 2 - JSON object input with multiple fields.

If the JSON input is a JSON object with multiple fields, <Item> is created as a root node. The element name Item is retrieved from the root node of the external document. Input

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

Output

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

Example 3 - JSON object with single field matching the document root node.

If the JSON input is a JSON object with a single field matching the document root node <Item>, the first field is assumed as the root. Input

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

Output

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

Example 4 - JSON object with single field matching document.

If the JSON input is a JSON object with a single field matching document, the first field is assumed as the root. Input

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

Output

<document>
  <Item>
    <No>1000</No>
    <Description>Bicycle</Description>
  </Item>
</document>

Example 5 - JSON object with single field not matching document or document root node.

If the JSON input is a JSON object with a single field not matching document or the external document root node, a wrapping <document> is added. Input

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

Output

<document>
  <Custom>
    <Item>
      <No>1000</No>
      <Description>Bicycle</Description>
    </Item>
  </Custom>
</document>