The Transform DataClosed Transform Data is a Format tool that enables a new structure to be created from an existing XML or recordset data source. The output generated by this tool is ready for use in a destination system which consumes or requires XML. tool does not require any global configuration; it can be immediately added to a task.

To add a new Transform Data step to an existing task:

  1. Open the relevant task (if creating a new task, see How to Create a Basic Task).
  2. Either:
► Click and drag the Transform Data icon from the Task Browser to the task Design area.

–OR–

► From the task's Design tab, right-click on empty space and select New > Format > Transform Data.
  1. In the General tab, Name your task step and choose your Data Source.

    The General tab is used to Name and describe (Description) this task step.

    Tip: If this task instance makes use of two or more Transform Data steps, ensure the Name used is unique for each individual step.

    Choose your Data source. This can either be:

    ►No Data Source — This allows you dynamically create the required XML document in the step itself — you do this in the Main tab
    ►Use a recordset or XML output from another step — Enable this parameter if recordset data or an XML document from a previous task step is to be transformed
    ■Source — Contains all available inputs from steps previously created in the task

    For more information, go to the main General tab topic.

  2. Go to the Main tab and create the output XML structure.

    Transform Data - Main Tab

    Click the image to view full size.

    The structure you create here appears in the Mapping tab and is ultimately made available to subsequent steps in the task. You can create this by doing one of the following:

    ►Replicate the structure from the input data source — If using an input source from a previous task step, you can replicate the already provided XML structure or use the recordset column names as the XML elements
    ►Import an existing XML structure — If you have an existing XML or XSD file containing the XML structure you would like this task step to use, click Import to replicate the structure
    ►Manually create the structure from scratch — Use Add Node, Add Element, and Add Attribute to manually create the required XML structure

    If required, click Export to use the final XML structure defined here in another Transform Data task step, or as a backup.

    For more information, go to the main Main tab topic.

  3. Go to the Mapping tab and map the input data source to the output elements.

    Transform Data - Mapping Tab

    Click the image to view full size.

    From here you can:

    ►Automatically map where input and output parameter names match
    ►Create mappings from a set of transform functions to change the data between input and output
    ►Use nested looping to support hierarchical data structures
    ►Import and export mappings so that they can be reused in other steps

    The left-hand Input Data pane shows those data source fields available for mapping . The right-hand Output Data pane displays those fields for the selected Object and Operation combination.

    TIP: If No data source was selected, you can create the input source in the Transformation Mappings pane by assigning variables to a Fixed / Dynamic function.

    Creating Mappings

    You create links by dragging and dropping a Input Data field onto its corresponding Output Data. Only linked fields are used in the output XML.

    Each operation has an additional field, SupplementaryReference, which allows for traceability when transferring data from one place to another. When mapped, the data resides locally at runtime. It is added to the output, and creates a record for reference purposes only — you can choose to map any field to SupplementaryReference to assist with checking where the data originated from or at what time the data transfer occurred, for example.

    Using Functions

    The Mapping tab makes use of the Data Transformation Layer (DTLClosed When used as part of a BPA Platform tool, the Data Transformation Layer (DTL) extend the tool's functionality by transforming SML from schema to another. In addition, several transformation functions are available for use with the input data before such data is mapped to the destination schema.) feature of BPA Platform, where you can use available functions to manipulate the data. Use the Functions pane to add transformation functions:

    Function TypeFunctionDescription
    Aggregation—Aggregation functions define operations for specific nodes.
      Node CountThis function counts the number of occurrences of a node in the input recordset or XML document. The result is then passed to the mapped output node. For more information, see Introducing the Node Count Function.
      SumThis function calculates the total of the values from all iterations of an element in the input recordset or XML document. The sum of the values is used as the new value to an output element. For more information, see Introducing the Sum Function.
    Data—Data functions perform operations on input data to generate new output data.
      Fixed / DynamicThis function passes a static or dynamic value to the output XML schema. Use a variable or recordset column to generate the dynamic data. For more information, see Introducing the Fixed / Dynamic Function.
      Run VBScriptClosed The Run VBScript tool is used to create a task step that runs a Visual Basic Script using data produced by other steps in the task.Use this function to perform VBScript operations to process input data, generate output data, or both. For more information, see Introducing the Run VBScript Function.
    Lookup—Lookup functions are used to find values in a nominated source, by a key.
      External LookupUse this function to lookup values from an external database. This uses existing Database Query (ODBC)Closed You typically use the Database Query (ODBC) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. or Database Query (OLEDB)Closed You typically use the Database Query (OLEDB) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. global connections. For more information, see Introducing the External Lookup Function.
      Internal LookupUse this function to find an alternative value for input data from a predefined lookup table. For more information, see Introducing the Internal Lookup Function.
    Looping—Looping functions loop through the input recordset or XML document to perform functions on all iterations of a node.
      Interleaved MergeThis function loops through the input recordset or XML document and merges data from specified elements into a single occurrence for the mapped output data. For more information, see Introducing the Interleaved Merge Function.
      Simple LoopThis function loops through the input recordset or XML document and creates an output data node for every iteration of an input node it finds. For more information, see Introducing the Simple Function.

    Additional Functionality

    In the Transformation Mappings pane, click:

    to import and use an existing mappings file (.DTLX) from a previous task

    to export the current mappings for use in another task or as a backup (.DTLX)

    to reset all current mappings

    to delete the highlighted mapping

    to delete the highlighted function

    Adding an External Lookup Function

    External Lookup functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting ServicesClosed The Run Microsoft Reporting Services tool automates the running of Microsoft Reporting Services (MSRS) reports.
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Lookup node and drag External Lookup to the Transformation Mappings pane.
    3. Drag the relevant Input Data source node or element to the new icon.
    4. Do the same for the receiving Output Data node or elements.
    5. Double-click the icon.

    The Transformation Mappings pane is replaced with the External Lookup's configuration pane:

    Data Transformation Layer - External Lookup

    1. Enter a meaningful Function Name.
    2. The input (source) and output (destination) links created previously now appear in the Inputs and Output tabs. If you have more than one connected input or output, it is recommended you Edit each Parameter in turn and rename it.
    3. To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    Specifying the Data Source

    The External Lookup function uses existing Database Query (ODBC) or Database Query (OLEDB) global connections; if one doesn't already exist, you must create the connection to the external data source first. If you have an existing connection to the external data source, go to Connecting to the External Data Source.

    Creating the External Data Source

    You can only connect to your external data source using an ODBC or OLEDB driver; both are accepted by the External Lookup function.

    Creating an ODBC External Data Source

    You do this in the ODBC Connections interface which sits outside the DTL. You open this interface from the resources tree — expand System > Tools > Data Connectors or Input, and double-click Database Query (ODBC) in the items list.

    1. Click Add to create a new connection and select the required ODBC driver or data source.
    2. Expand ODBC Drivers to see all ODBC drivers available on the client computer.

      Note: Any computer hosting a BPA Platform client that runs tasks using this connection, including the BPA Platform server, must have the selected driver installed on the computer. Without the driver, the task will fail.

    3. Once a driver has been selected, add the connection details to the database. Note that the configuration interface is different for every driver available.

    For more information, see Adding ODBC Connections.

    Creating an OLEDB External Data Source

    You do this in the OLEDB Connections interface which sits outside the DTL.

    You open this interface from the resources tree — expand System > Tools > Data Connectors or Input, and double-click in the items list.

    Note: It doesn't matter which tool category you select the tool from, the same interface is opened.

    1. Click Add to create a new connection; select the required OLEDB driver or data source.
    2. Expand OLEDB Drivers to see all OLEDB drivers available on the client computer.

      Note: Any computer hosting a BPA Platform client that runs tasks using this connection, including the BPA Platform server, must have the selected driver installed on the computer. Without the driver, the task will fail.

    3. Once a driver has been selected, add the connection details to the database. Note that the configuration interface is different for every driver available.

    For more information, see Adding OLEDB Connections.

    Connecting to the External Data Source

    1. Reopen the relevant task step and go to the Mapping tab.
    2. Double-click the icon and go to the Data Source tab.
      1. From the Connection drop-down, choose the connection to your external data source.
      2. From the Data Object tree, double-click the table containing the lookups.
    3. Go to the Columns tab.
      1. From the input Mapped Data Source, choose the column containing the values received from the input data source.
      2. From the output Mapped Data Source, choose the column containing the expected values of the destination system.

    Data Transformation Layer - External Lookup - Columns Tab

    In the example above, when a value is received that matches one listed in the TypeID column, the corresponding value in the TypeName column is sent to the destination.

    1. (Optional) Go to the Query tab. This contains the auto-generated SQL script for querying the lookup table (Data Source and Columns).
      1. Click Test.
      2. In Parameter Value, enter an example value for the input lookup.
      3. Click Execute to check that the returned value is correct.

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    If the external lookup to the database table returns more than one value for the output parameter, you can choose to:

    ► Use first value returned — All other records containing the other values are ignored.
    ► Assign value — All returned values are ignored; instead the value entered here is used.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    Click OK to save the function and return to the parent task step's Mapping tab.

    Adding a Fixed / Dynamic Function

    Fixed / Dynamic functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting Services
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Data node and drag Fixed / Dynamic to the Transformation Mappings pane.
    3. Drag the relevant Input Data source node or element to the new icon.
    4. Do the same for the receiving Output Data node or elements.
    5. Double-click the icon.

    The Transformation Mappings pane is replaced with the Fixed / Dynamic's configuration pane:

    Data Transformation Layer - Fixed / Dynamic

    1. Enter a meaningful Function Name.
    2. The output (destination) link(s) created previously now appear in the Output tab. If you have more than one connected output, it is recommended you Edit each Parameter in turn and rename it.
    3. To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    Specifying a Fixed Value

    Fixed values are set at configuration time and persist throughout every task run. These can take the form of literal or constant values.

    1. Go to the Value tab.
    2. In the box provided, enter the required fixed value.

    Specifying a Dynamic Value

    Dynamic values change each time the task is run, or multiple times throughout a single task run. Values are provided by other task data such as variables, functions, and formulas.

    1. Go to the Value tab.
    2. From the Task Browser, drag the required dynamic parameter to the box provided.

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a dynamic value is unavailable, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    Click OK to save the function and return to the parent task step's Mapping tab.

    Adding an Interleaved Merge Function

    Interleaved Merge functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting Services
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Looping node and drag Interleaved Merge to the Transformation Mappings pane.
    3. Drag the relevant Input Data source node or element to the new icon.
    4. Do the same for the receiving Output Data node or elements.
    5. Double-click the icon.

    The Transformation Mappings pane is replaced with the Interleaved Merge's configuration pane:

    Data Transformation Layer - Interleaved Merge

    1. Enter a meaningful Function Name.
    2. The input (source) and output (destination) links created previously now appear in the Inputs and Output tabs. If you have more than one connected input or output, it is recommended you Edit each Parameter in turn and rename it.
    3. To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    In the example above, we are taking inputs for First_Name and Last_Name and merging them, presumably to output to a Name element.

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    If the number of received inputs nodes doesn't match the number of mapped input nodes, you can choose to:

    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.
    ►Produce output for each element in a last cardinal input — The function does its best to fulfil all mappings and ignores any errors it encounters.

    Click OK to save the function and return to the parent task step's Mapping tab.

    Adding an Internal Lookup Function

    Internal Lookup functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting Services
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Lookup node and drag Internal Lookup to the Transformation Mappings pane.
    3. Drag the relevant Input Data source node or element to the new icon.
    4. Do the same for the receiving Output Data node or elements.
    5. Double-click the icon.

    The Transformation Mappings pane is replaced with the Internal Lookup's configuration pane:

    Data Transformation Layer - Internal Lookup

    1. Enter a meaningful Function Name.
    2. The input (source) and output (destination) links created previously now appear in the Inputs and Output tabs. If you have more than one connected input or output, it is recommended you Edit each Parameter in turn and rename it.
    3. To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    Creating the Lookup Table

    You use the Table tab to create the "translations" between the input and output elements.

    1. If you haven't renamed the input and output parameters, it is recommended you do so now (Input and Output tabs). This helps identification when adding the values.
    2. Go to the Table tab.
    3. Enter the lookup input - output value pairs as required.

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    Click OK to save the function and return to the parent task step's Mapping tab.

    Adding a Node Count Function

    Node Count functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting Services
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Aggregation node and drag Node Count to the Transformation Mappings pane.
    3. Drag the parent Input Data source node to the new icon. This is the "folder" containing the element you want to count.
    4. Drag the Input Data element whose values you want to count to the icon. Note that all values are counted, no value distinction is made.
    5. Drag the receiving Output Data element to the icon.
    6. Double-click the icon.

    The Transformation Mappings pane is replaced with the Node Count's configuration pane:

    Data Transformation Layer - Node Count

    1. Enter a meaningful Function Name.
    2. Make sure the CountWithin and ItemToCount mappings are correct (hover over each Mapped To parameter in turn).

      To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    Click OK to save the function and return to the parent task step's Mapping tab.

    Adding a Run VBScript Function

    Run VBScript functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting Services
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Data node and drag Run VBScript to the Transformation Mappings pane.
    3. Drag the relevant Input Data source node or element to the new icon.
    4. Do the same for the receiving Output Data node or elements.
    5. Double-click the icon.

    The Transformation Mappings pane is replaced with the Run VBScript's configuration pane:

    Data Transformation Layer - Run VBScript

    1. Enter a meaningful Function Name.
    2. To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    Adding the VB Script Function

    Go to the VB Script tab and add the code for the function. Use Check Syntax to check for errors.

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    Click OK to save the function and return to the parent task step's Mapping tab.

    Adding a Simple Function

    Simple functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting Services
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Looping node and drag Simple to the Transformation Mappings pane.
    3. Drag the relevant Input Data source node to loop through to the new icon.
    4. Do the same for the receiving Output Data node or elements.
    5. Double-click the icon.

    The Transformation Mappings pane is replaced with the Simple's configuration pane:

    Data Transformation Layer - Simple

    1. Enter a meaningful Function Name.
    2. To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    Click OK to save the function and return to the parent task step's Mapping tab.

    Adding a Split by Type Function

    Split by Type functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting Services
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Looping node and drag Split by Type to the Transformation Mappings pane.
    3. Drag the Input Data node containing the records you want to split to the icon.
    4. Drag the Input Data element containing the values you are basing the split on (for example, an Address_Type element if you want to split based on values Delivery or Billing) to the icon.
    5. Do the same for the receiving Output Data node.
    6. Double-click the icon.

    The Transformation Mappings pane is replaced with the Split by Type's configuration pane:

    Data Transformation Layer - Split by Type

    1. Enter a meaningful Function Name.
    2. Make sure the ObjectToSplit and SplitBasedOn mappings are correct (hover over each Mapped To parameter in turn).

      To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    3. The output (destination) link(s) created previously now appear in the Output tab. If you have more than one connected output, it is recommended you Edit each Parameter in turn and rename it.

    Defining the Split

    If you haven't renamed the Output parameters, it is recommended you do this now. This makes it easier to identify mappings.

    1. Go to the Mapping tab.
    2. In Split Based On Value, enter the value that indicates this record is outputted to this output node. In this example, where a 1 is received in the designated input element (SplitBasedOn), the record is outputted to the mapped Billing node:

    Data Transformation Layer - Split by Type - Mapping Tab

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    If a value is detected in the SplitBasedOn element (Inputs tab) that is not specified in the Mapping tab, you can choose to:

    ►Create Output — Map the record to the output parameter specified here.
    ►Create no output for this object instance and continue — The function ignores the unexpected value and continues looping or, if at the end of the loop, continues with the task step.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    Click OK to save the function and return to the parent task step's Mapping tab.

    Example Usage

    Let us say this is the XML we receive as the input:

    <company>
  <id>1</id>
  <address>
    <line1>7 American Ash Court</line1>
    <line2>London</line2>
    <is_shipping>1</is_shipping>
  </address>
</company>
<company>
  <id>1</id>
  <address>
    <line1>98298 Kim Place</line1>
    <line2>Edingburgh</line2>
    <is_shipping>0</is_shipping>
  </address>
</company>
<company>
  <id>2</id>
  <address>
    <line1>67787 Thackeray Park</line1>
    <line2>Cardiff</line2>
    <is_shipping>1</is_shipping>
  </address>
</company>

    And this is the expected outputted XML structure:

    <company_address>
  <id>1</id>
  <shipping_address>
    <line1>7 American Ash Court</line1>
    <line2>Edinburgh</line2>
  </shipping_address>
  <billing_address>
    <line1>9 Green Ridge Street</line1>
    <line2>London</line2>
  </billing_address>
</company_address>

    In the Transform Data tool, we have replicated the required output structure (Main tab):

    Transform Data - Main Tab - Split By Type Output Structure

    A few things to note:

    ►shipping_address and billing_address nodes both have minOccurs set to 0. This is because we cannot guarantee each customer record has both.
    ►line1 and line2 for both address types have minOccurs and maxOccurs both set to 1. These cannot be repeated for a single address type.

    The Mapping tab is structured as follows:

    Transform Data - Mapping Tab - Split By Type Function Example

    Click the image to view full size.

    ►A Simple function connects the parent company input node to the company_address output node

    This function loops through the XML and creates a company_address record for every company record it finds.

    ►line1 and line2 from the input XML are mapped to both their corresponding elements in the shipping_address and billing_address nodes

    Let's take a look at the configuration for the Split by Type function:

    ►The inputs are as follows:

    Transform Data - Split By Type Function - Inputs

    where for every record found in address, a corresponding record is made in the output schema based on the value found in is_shipping.

    ►The outputs are as follows:

    Transform Data - Split By Type Function - Outputs

    These are the nodes in the output schema where the address records are mapped to. Note how the default parameter names have been changed— changing the parameter names from "Output0" allows you to quickly understand which value should be mapped here.

    ►Finally, the Mapping tab determines which records go to shipping_address and which to billing_address:

    Transform Data - Split By Type Function - Mappings

    Tip: Want to try this yourself? Download the input XML file here;note that this open the file in a new browser where you can copy the text out of.

    Adding a Sum Function

    Sum functions are used in the Mapping tab of the following tools:

    ►Microsoft SQL Server Connector
    ►Run Microsoft Reporting Services
    ►Transform Data
    ►Web Service Connector
    1. Open the relevant task step and go to the Mapping tab.

    — Node

    — Element

    and — Node or element available for mapping

    — Mandatory element that must be mapped and included in the output

    1. Click Functions to expand the Functions pane.
    2. Expand the Aggregation node and drag Sum to the Transformation Mappings pane.
    3. Drag the parent Input Data node to the new icon. This is the "folder" containing the elements you want to generate a total for.
    4. Drag the Input Data element whose values you want to total to the new icon.
    5. Do the same for the receiving Output Data node or elements.
    6. Double-click the icon.

    The Transformation Mappings pane is replaced with the Sum's configuration pane:

    Data Transformation Layer - Sum

    1. Enter a meaningful Function Name.
    2. Make sure the SumWithin and ItemToSum mappings are correct (hover over each Mapped To parameter in turn).

      To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

    Selecting Error Handling Options

    Use the Options tab to define error handling behaviour for this function.

    At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

    ►Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
    ►Output no Data — The element isn't included in the output XML.
    ►Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
    ►Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

    Click OK to save the function and return to the parent task step's Mapping tab.

  4. Go to the Options tab and select the relevant error handling options for this task step.

    The Options tab allows you to define how errors in this step are handled at task runtime.

    If an error occurs, you can decide whether the step should Continue processing, or terminate the step immediately (Abort Step).

    If the step is aborted, you can choose to Continue processing onto the next step in the task, or terminate the whole task immediately (Abort Task). By allowing the task to Continue, you can use the error XML received back in a Save FileClosed The Save File tool saves output from another task step capable of producing document, as one or more files on the local drive, on-premise file server, external server, or cloud based server. step for investigation purposes, for example.

    By default, the XML document is outputted with <OutputData> as the header node with a standard namespace declared. If required, you can choose to output the XML with no namespace declaration or with a custom header node. This is particularly useful if you want to directly use the XML in an external system.

    To change the Output Data Structure settings, enable Output raw XML — this setting alone removes the namespace declaration.

    If a different header node is required, also enable Set custom header node and specify it below using the format:

    <custom_header_name></custom_header_name> or <custom_header_name/>

    replacing custom_header_name with the required header; to use no header node, leave this blank.

    In either circumstance, use the CustomXMLOutput property rather than the OutputXML property when mapping and saving the output to file.

    For more information, go to the main Options tab topic.

  5. Click  to save the task step.

Want to watch the Transform Data tool being configured? Go to the Codeless Platforms Video Library.