Schema processing in Jitterbit Integration Studio

Introduction

In certain circumstances, data in schemas may be converted during processing. This page describes when and how data is converted depending on the schema type:

• All schema types
• CSV
• JSON
• XML

All schema types

In most cases (exceptions are noted below), certain data is converted in the same manner regardless of the schema type:

• Data types
• Names of fields and nodes
• Null values

Data types

These sections describe Jitterbit's support of certain data types by type of schema. For all data types supported in Jitterbit Scripts, see Data types in Jitterbit Script.

Unlimited-precision data types

For most schema types, unlimited-precision data types, such as XML decimal, are converted to double data types and therefore have a precision limit imposed on them that could, as a result, truncate data.

The precision limit is within the range of the minimum and maximum values of a signed long, which is –2,147,483,648 to 2,147,483,647. If outside of this range, consider using a string data type instead to avoid truncating data.

Non-primitive data types

The primitive data types boolean, date, double, float, integer, long, and string, and the non-primitive data type decimal are supported in all schema types. When creating or editing a custom flat or hierarchical schema or editing any schema uploaded in a transformation, these data types are available to choose in the Type dropdown. For new custom schemas, other non-primitive data types such as datetime are not supported.

However, other non-primitive data types are supported for schemas that are automatically generated from a connector-generated schema or were mirrored from such a schema. After they are generated, such schemas can also be manually edited in the custom schema editor. If the schema contains any non-primitive data types, these are also listed in the Type dropdown when editing such a schema.

Names of fields and nodes

Names of fields and nodes are converted to valid namespaces following the XML standard. For reference, see NCNameChar for the allowed characters as defined by the W3C.

When mirroring a schema provided by a database, any special characters in a schema field or node name are replaced by underscores (_) as required to create a valid XML schema.

Null values

Fields that have a null value are included in the resulting data schema despite having no data. As they also have no defined data type, these fields are treated as having a string data type.

CSV schemas

When using a CSV schema, certain rules apply to the following data:

• CSV files with headers
• Special characters

CSV files with headers

When providing a CSV file with a header row as a sample schema file, these rules are applied to generate column names:

• Special characters are replaced with a question mark (?).
• Spaces are replaced with an underscore (_).
• If a column name is blank, it is replaced with f1, f2, f3, and so on.
• If a column name starts with a number, it is prefixed with an underscore (_).
• If a column name is repeated, it is appended with 2, 3, 4, and so on.

Special characters

In these circumstances special characters (. _ - $ # @ ?) in CSV schemas are converted:

• Special characters present on the source or target side of a transformation are replaced by a question mark (?).
• Special characters in a script on a target field in a transformation are replaced by a question mark (?). This rule also applies to any custom flat or custom hierarchical schema created in CSV format.

Note

These conversions are limited to the schema structures only and do not affect the actual data.

JSON schemas

When using a JSON schema, certain rules apply to the following data:

• Data types (as described above)

• Names of fields and nodes (as described above)

  Caution

  When a JSON schema's field or node name begins with a number or an @ character, that character is replaced with an underscore _. For example, a field name of 12345 is converted to _2345, and a field name of @account is converted to _account.

• Null values (as described above)

XML schemas

When using an XML schema, certain rules apply to the following data:

• Namespaces
• Self-closing tags
• Special characters

For information on troubleshooting certain XML schema and transformation errors, see Operation troubleshooting.

Namespaces

Namespaces in XML schemas are supported. If more than one namespace is used in an XML schema, the Jitterbit agent converts the XML schema to multiple XSD files during processing.

Self-closing tags

Self-closing tags on elements in XML schemas are supported with some manipulation of the sample XML used to generate the schema. To send the XML element in the payload without a mapped value, you can use a Jitterbit function and Jitterbit variable as described below.

Manipulate the sample XML

Manipulation is required when an XML sample file uses shorthand notation for an opening and closing tag in an element such as this:

<tag/>

If such shorthand is used in an XML schema directly, the node will not be able to be mapped to when used as the target schema of a transformation.

To resolve, manipulate the sample XML being used to generate the schema to expand the opening and closing tags surrounding the element, and provide a sample value so that a data type is assigned to the element:

<tag>example</tag>

The element will then show up as a field in the XML schema and, when used as the target schema in a transformation, you can map to that field.

Map to the XML field

If you do not have a source object or variable to map to the target field, you can place the Jitterbit function Null in the transformation script to use as the mapped value:

<trans>
Null()
</trans>

Upstream of the transformation using the XML schema, set one of these Jitterbit variables (depending on which is appropriate to your use case) to control what is sent:

• Empty XML: To send an empty XML node in the payload, use jitterbit.target.xml.include_empty_xml:

  $jitterbit.target.xml.include_empty_xml=true;
  

• Null XML: To send a nil value in the XML payload, use jitterbit.target.xml.include_null_xml:

  $jitterbit.target.xml.include_null_xml=true;
  

• Exclude Empty XML: To exclude an empty XML node with a boolean data type, use jitterbit.target.xml.exclude_empty_data:

  $jitterbit.target.xml.exclude_empty_data=true;
  

Special characters

In these circumstances special characters in XML schemas are converted:

• Special characters in a script on a target field in a transformation are replaced by a question mark (?).

• These special characters in an XML schema field or node name are not supported, as they are not supported by XML:

  $ # @ ?

Note

These conversions are limited to the schema structures only and do not affect the actual data.