Business Integration Solutions Documentation

About: XMLTOJSONTRANSLATOR Activity

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

Usage

Using this activity you can translate an XML document into a JSON document.

Concept

Internal processing in Microsoft Dynamics BC happens on documents in XML format. However, if your publishing company demands data in JSON format, then you must use the XML to JSON Translator activity to translate the document from an XML format into a JSON format.

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

Setup

XmlToJsonSetup

Setup for this activity is split into 3 sections.

General Setup

Field Description
Source Document Type The document type of the document representing the XML to be translated.
Source Document No The document no of the document representing the XML to be translated.
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.
Text Encoding The encoding applied to the resulting document, default UTF-8

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.

Policies

The following policies are available for XML to JSON.

ParseDataTypes

Accepts True or False.

Determines whether to convert data types into primitive JSON types. To determine whether a field can be translated into a primitive JSON type, the field data type in the document must not have type text.

Examples

<Item>
    <No>1000</No>
    <UnitPrice>99.55</UnitPrice>
    <AllowInvoiceDisc>true</AllowInvoiceDisc>
</Item>

ParseDataTypes: True

{
    "Item": {
      "No": "1000",
      "UnitPrice": 99.55,
      "AllowInvoiceDisc": true
    }
}

ParseDataTypes: False

{
    "Item": {
        "No": "1000",
        "UnitPrice": "99.55",
        "AllowInvoiceDisc": "true"
    }
}

ParseNull and NullValue

ParseNull accepts True or False.

ParseValue accepts a text value. This value will be ignored if ParseNull is set to False.

Determines how an empty element value is translated to JSON.

Examples

<Item>
    <No>1000</No>
    <CustomField></CustomField>
</Item>

ParseNull: False

{
    "Item": {
      "No": "1000",
      "CustomField": {}
    }
}

ParseNull: True, NullValue: ''

{
    "Item": {
      "No": "1000",
      "CustomField": null
    }
}

ParseNull: True, NullValue: -NULL-

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

AttributeBlockName and AttributePrefix

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

If AttributeBlockName has a value, an additional object is added to the parent object to list the attributes.

If AttributePrefix has a value, attribute names are added with a prefix.

Examples

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

AttributeBlockName: '', AttributePrefix: _

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

AttributeBlockName: Attributes, AttributePrefix: _

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

AttributeBlockName: Attributes, AttributePrefix: ''

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

StripLevels

Accepts a number.

Determines the starting point of the object. Useful when the xml document needs to be stripped of opening elements.

Examples

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

StripLevels: 0

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

StripLevels: 1

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

StripLevels: 2

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

StripLevels: 3

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

Levels cannot be stripped further if the element contains more than one child element. In the examples above, a Strip Levels of 3 got the same result as a Strip Levels of 2.

It is possible to also get an array as the root item in a json output, as shown below.

Input

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

StripLevels: 0

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

StripLevels: 2

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

TextAlwaysAsProperty and TextNodeName

TextAlwaysAsProperty accepts True or False

TextNodeName accepts a text string. Must have a value.

If TextAlwaysAsProperty is set to True, a text node is wrapped with a field with the name specified in TextNodeName. If an element has a text node, together with other nodes (mixed node), they are always wrapped with the field specified in TextNodeName.

Examples

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

TextAlwaysAsProperty: False

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

TextAlwaysAsProperty: True, TextNodeName: TEXT

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

Node Policies

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

TreatAsArray

This policy ensures that a JSON array is outputted for a specific node, even if there is only one item in it.

Examples

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

TreatAsArray for the Item node unchecked

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

TreatAsArray for the Item node checked

{
  "document": {
    "Item": [
      {
        "No": "1000"
      }
    ]
  }
}

WrapArray

This policy allows for unwrapping of a list for a simpler structure in JSON, if possible. This is only taken into consideration if TreatAsArray is enabled for the node.

Examples

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

WrapArray for the Item/Comments/Comment node checked

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

WrapArray for the Item/Comments/Comment node unchecked

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

The XmlToJson translator supports the option to promote a node's value as a json object header. This will allow the json files to have object keys that are not valid on an xml document. The property is setable on the linked document level, and is only available for nodes that are not blocks. Only one promoted line is supported per document block.

PromotedProperty

Depending on the node policies set at the block level on the translator, the promoted property behaves differently.

The below properties can be amended to accomodate for your business scenario

Examples

<document>
  <Item NavRecordId="Item: 1000">
    <No>1000</No>
    <Description>Bicycle</Description>
    <AllowInvoiceDisc>true</AllowInvoiceDisc>
    <UnitPrice>4000</UnitPrice>
  </Item>
  <Item NavRecordId="Item: 1001">
    <No>1001</No>
    <Description>Touring Bicycle</Description>
    <AllowInvoiceDisc>true</AllowInvoiceDisc>
    <UnitPrice>4000</UnitPrice>
  </Item>
</document>

Promoted for the Item/No node checked, TreatAsArray and WrapArray for the Item node unchecked

{
	"document": {
		"Item": [
			{
				"1000": {
                  "_NavRecordId": "Item: 1000",
                  "Description": "Bicycle",
                  "AllowInvoiceDisc": true,
                  "UnitPrice": 4000
                }
			},
			{
				"1001": {
                  "_NavRecordId": "Item: 1001",
                  "Description": "Touring Bicycle",
                  "AllowInvoiceDisc": true,
                  "UnitPrice": 4000
                }
			}
		]
	}
}

Promoted for the Item/No node checked, TreatAsArray for the Item node checked, WrapArray unchecked

{
	"document": [
		{
			"1000": {
                "_NavRecordId": "Item: 1000",
                "Description": "Bicycle",
                "AllowInvoiceDisc": true,
                "UnitPrice": 4000
              }
		},
		{
			"1001": {
                "_NavRecordId": "Item: 1001",
                "Description": "Touring Bicycle",
                "AllowInvoiceDisc": true,
                "UnitPrice": 4000
              }
		}
	]
}

Promoted for the Item/No node checked, TreatAsArray and WrapArray for the Item node checked

{
	"document": {
		"Item": [
			{
				"1000": {
                  "_NavRecordId": "Item: 1000",
                  "Description": "Bicycle",
                  "AllowInvoiceDisc": true,
                  "UnitPrice": 4000
					      }
			},
			{
				"1001": {
                  "_NavRecordId": "Item: 1001",
                  "Description": "Touring Bicycle",
                  "AllowInvoiceDisc": true,
                  "UnitPrice": 4000
                }
				]
			}
		]
	}
}

No property is checked

{
	"document": {
		"Item": [
			{
				"_NavRecordId": "Item: 1000",
				"No": "1000",
				"Description": "Bicycle",
				"AllowInvoiceDisc": true,
				"UnitPrice": 4000
			},
			{
				"_NavRecordId": "Item: 1001",
				"No": "1001",
				"Description": "Touring Bicycle",
				"AllowInvoiceDisc": true,
				"UnitPrice": 4000
			}
		]
	}
}