Version 22.0.8473

• Dynamic Content
• Formatters
• String Manipulation
• Date Manipulation
• Math Operations
• Lookahead
• Treat Empty As Null

The expression editor supports modifying values as they are mapped from the source to the destination. This editor makes use of the powerful ArcScript language to format and dynamically generate content. To access the Expression Editor, select a node in the destination document and select the tablet and pencil icon to display the editor.

The editor contains a panel with the ArcScript expression that is used to render the result. When editing a node that already has been mapped to an element from the source XML, the expression displays the xpath representing this mapping. From here, edit the expression to manipulate the value, or include references to additional nodes in the source XML.

Dynamic Content

Any expression in square brackets is evaluated as a variable in ArcScript. In most situations, variable expressions include an xpath() evaluation of an element in the source document. Multiple bracketed expressions can be used to express multiple variables, either back-to-back or interspersed with literal characters (outside of square brackets).

For example, to combine the values at two different paths:

<Customer>
<First>Bruce</First>
<Last>Wayne</Last>
</Customer>

A single expression can join the two values:

[xpath('Customer/First')] [xpath('Customer/Last')]

Formatters

Formatters support manipulating the values returned at different xpaths. Formatters are separated by a pipe character (|) in the expression, and evaluated from left to right. For example:

[xpath('City') | toupper | substring(0,3)]

In this example, before the value of the value at the City xpath is returned, all string characters are converted to upper case characters, and a substring of the first three characters are returned in the result. For example, if the source document had a value of:

<City>Durham</City>

The resulting expression returns the following:

DUR

After selecting the Formatters tab in the Expression Editor, each formatter is displayed in a searchable list. A formatter can be added to the expression directly by clicking on the formatter from the list.

String Manipulation

String manipulation is a common use cases for the Expression Editor. Common string formatters include:

• split
• toupper
• tolower
• substring
• regexmatch

For example, it may be necessary to split the a Name value from the input XML into two separate fields of the output XML.

In this case, the split formatter should be used. The parameters of this formatter are the character around which to split the string and the index of the resulting array that should be returned (indexes begin at 1):

[xpath(CustomerName) | split(' ', 1)]

The full list of string formatters can be found here.

Date Manipulation

Another common use case involves reformatting dates from the source document to the destination. This is supported by the todate formatter, which accepts two arguments: the format of the output and input dates. The following example converts a date in the form of 12/21/18 to a date in the form of Friday, 21 December, 2018:

[xpath(PurchaseDate) | todate(D, "mm/dd/yy")]

Additional functions that are useful for date calculation are dateadd and compare, which can be used to add or subtract fixed periods of time to a date and perform date comparisons, respectively.

The full list of date formatters can be found here.

Math Operations

Math operations are useful for performing calculations on numerical values from the source XML. The following example converts cents to dollars, and ensures that the resulting value is a decimal value with two positions:

[xpath(ItemCost) | divide(100) | decimal(2)]

Math formatters can be used to calculate tax and add the tax value to a total. The following example includes a nested set of math formatter expressions; each expression is evaluated from left to right, and a nested expression is evaluated in its entirety before returning to outer expressions:

[xpath(Subtotal) | divide(100) | multiply([xpath(TaxPercent) | divide(100) | add(1)]) | decimal(2)]

The full list of math formatters can be found here.

Lookahead

The xpath() formatter supports lookahead syntax to further specify which values from the source document should be mapped to the destination document. Lookaheads can help target a specific value in the midst of repeated XML element structures.

For example, the input XML may have multiple line items, only one of which contains the desired value. Each line item has the same xpath, so Lookahead syntax is required to retrieve the desired value from among the values at the same xpath.

The following XML demonstrates this situation, as the LineItem elements have matching XML structure:

<LineItem>
  <ItemType>Goods</ItemType>
  <ItemName>Widgets</ItemName>
  <ItemAmount>20.00</ItemAmount>
</LineItem>
<LineItem>
  <ItemType>Tax</ItemType>
  <ItemName>Sales Tax</ItemName>
  <ItemAmount>1.38</ItemAmount>
</LineItem>

Imagine that the amount for the ‘Tax’ line item (1.38) needs to be mapped to the destination document, but not the amount for the ‘Goods’ item (20.00). Since both line items have the same XML structure, an xpath alone is not enough to specify the ‘Sales Tax’ line item amount. As an illustration, the following expression uses the correct xpath but retrieves the ‘Goods’ item amount instead of the ‘Tax’ item amount (since the ‘Goods’ item amount is the first value that satisfies the xpath):

[xpath(LineItem/ItemAmount)]

In order to specify the ‘Tax’ line item, the expression needs to look into the LineItem element for the ItemType element, which identifies the line item as a ‘Tax’ item. The LineItem element is thus the ‘parent’ of the Lookahead, and the ItemType element is the ‘target’ of the Lookahead.

Lookahead syntax is as follows: inside the xpath expression, add square brackets directly after the ‘parent’ element of the Lookahead. Inside the square brackets, provide the xpath to the ‘target’ element of the Lookahead and use an equals expression to check the target value (note that the square brackets must be escaped with backslashes):

[xpath(LineItem\[ItemType='Tax'\]/ItemAmount)]

This translates to: “find the value from ‘LineItem/ItemAmount’ for the ‘LineItem’ element where ‘LineItem/ItemType’ is ‘Tax’. The expression would return the value 1.38.

After saving changes in the expression editor, the expression displayed in the Destination mapping should have green text to indicate a valid expression. If the expression is bold or italics black text, then a syntax issue is causing the expression to be evaluated as a literal or an invalid expression. Typically this is caused by not escaping reserved characters like square brackets, parentheses, or slashes.

Treat Empty As Null

The Expression Editor includes the option to treat empty input values (e.g. a string with a length of 0) as NULL output values. By default, this is false, and empty input values will be treated as the empty string: “”

As an example for when this setting may be useful, some mappings may interface with a database table that includes columns that do not accept NULL values. In these cases, empty string values may prevent errors while inserting to the database, or empty string values pulled from the database may need to be converted to NULL to better reflect the dataset.