Skip to content

Appendix 1 – Data Exports (SQL to XML) - Sage 200

Estimated time

Estimated time: 1 Hr

In this exercise you configure an export. It queries the Sage 200 database directly for customers and their delivery addresses, transforms the data a little and writes the result to an XML file.

Method

  1. Set Up a Shared Database Connection String
  2. Create the Export
  3. Add Hierarchy
  4. Filter the Dataset
  5. XML Writer
  6. Create Parent Transactions
  7. Create Child Transaction Types

Set Up a Shared Database Connection String

To export data from Sage 200 you query its database directly with SQL, which needs a connection string. See Lookups and counters in step 4 of the training.

A shared connection string is defined once and used in Database Readers, Database Writers and Lookups. You maintain it in one place, and integrations are easier to move from one IMan instance to another.

  1. Go to the Setup tab and press Database Connections.

  2. Create a new database connection:

    1. Enter a Connection ID.
    2. Enter a Description.
    3. Enter the corresponding connection string.

    Test checks the string before you save it. A green tick on the button means it works. A red cross means it does not, and the database driver's error appears to the right of the button.

  3. Press the green tick to save.

  4. Return to the Design tab.

Create the Export

  1. Create a new integration.
  2. Drag a Database Reader onto the integration and double-click it to open its setup. Give it a Transform Id of DB Read. The palette names it Database Reader, and a chain of four transforms is easier to read when each is named for what it does.

  3. Under Source, select the connection string from the Database Connection drop-down.

  4. Enter the following into the SQL Statement:

    select A.CustomerAccountNumber, A.CustomerAccountName, A.CreditLimit,
       A.AccountBalance, A.DateAccountDetailsLastChanged,
       D.Description, D.PostalName, D.AddressLine1, D.AddressLine2,
       D.AddressLine3, D.AddressLine4, D.PostCode, D.TaxNo
from SLCustomerAccount A
left join CustDeliveryAddress D
  on A.SLCustomerAccountID = D.CustomerID

    The join is an outer join, so the query still returns a customer with no delivery address. The Filter removes the empty delivery addresses later.

  5. Press Refresh.

  6. Press the Field Mapping tab and rename the transaction to Customers with the pencil beside it in the Transaction panel.

    The rename changes only the display name. The id beside it stays root, and every downstream transform still refers to root.

  7. Go back to the Setup tab.

  8. Press Refresh. The preview shows Customers:

  9. Press Close. A transform pane has no Save of its own. Closing it commits the pane to the integration, and the integration's own Save then stores it.

Add Hierarchy

  1. Drag a Hierarchy transform (the palette tile is Hierarchise) onto the integration, connect it to the Read transform and double-click it to open.

  2. Press the Field Mapping tab. Transaction Id to Hierarchise already reads Customers. IMan chooses it for you because the Read transform has only one transaction.

  3. Press Edit, then set the key field and deselect the detail fields:

    Field Setting
    CustomerAccountNumber Key field 1
    Delivery address fields Deselect all

    The delivery address fields are Description, PostalName, AddressLine1 to AddressLine4, PostCode and TaxNo.

  4. Press Save on the grid's toolbar.

    The confirmation looks worse than it is

    When you save with fields deselected, a Remove Fields prompt lists each of them and says they "will be deleted from the transform". IMan removes them only from the records this transaction passes downstream. The fields stay in the grid, unticked, and you can still select them on the child you create in the next step. Answer OK and carry on.

  5. Select Customers in the Hierarchy tree on the right.

  6. In the New Transaction Id textbox enter DeliveryAddress.

  7. Press the add button, the triangle beside the textbox (Add under the selected transaction). It stays disabled until the textbox has a name in it. It adds the new transaction under the selected one, so do not skip step 5.

  8. Press Edit, then select the detail fields and set the key fields for the new transaction:

    Field Setting
    CustomerAccountNumber Select, key field 1
    Delivery address fields Select all
    Description Key field 2

  9. Press Save. The same Remove Fields prompt appears for the customer-only fields. Answer OK.

  10. Press Refresh.

  11. Expand some of the records. Some customers have an empty delivery address. This is expected, because:

    • the SQL query uses a left outer join
    • where there are no delivery address records, the query still returns the customer record, with null or empty delivery address values.

In the next step a Filter transform removes the empty records.

Filter the Dataset

  1. Add a Filter transform to the integration, connect it to the Hierarchy transform, and double-click it to open.

  2. Move to the Field Mapping tab and select DeliveryAddress in the Current Transaction Id drop-down.

  3. Enter the filter into Record Evaluation:

    %Description <> ""

  4. Click outside the editor to commit the filter, then press Refresh. The empty delivery addresses are gone. The customer is still there, and its DeliveryAddress transaction now says No records to display.

XML Writer

  1. Add an Xml Writer to the integration and connect it to the Filter transform.

  2. Double-click it to open, and set the file options:

    Field Value
    Target The destination of the file. For training, select File
    File Path C:\IMan\OutputData
    File Name Customers.xml

    Leave the other settings as they are.

Create Parent Transactions

  1. Open the Field Mapping tab.

  2. Set the Initial XPath to /Customers.

  3. Set the Transaction Type XPath to Customer, the singular node that repeats inside /Customers.

  4. Double-click the CustomerAccountNumber row to edit it, tick Export Field and set its XPath to CustomerId. A newly added Xml Writer has Export Field unticked on every field, so each row needs both settings.

  5. Press the green tick to save.

  6. Repeat for each of the remaining fields, ticking Export Field on every one, and set the XPaths as below:

    Field Name XPath
    CustomerAccountNumber CustomerId
    CustomerAccountName AccountName
    CreditLimit CreditLimit
    AccountBalance Balance
    DateAccountDetailsLastChanged LastChanged

  7. Press Refresh.

The child transaction has no XPaths yet, so IMan writes none of its data. The run still completes, and you can look at the parent half of the document. Open the XML file at the path and file name you set on the Setup tab. It should have a node per customer, with an individual node per field:

Create Child Transaction Types

  1. Select DeliveryAddress from the Current Transaction Id drop-down.

  2. Set the Transaction Type XPath to DeliveryAddresses.

  3. Edit the CustomerAccountNumber field and leave Export Field unticked. That keeps the field out of the file. It is here only because it is the key that joins the child to its parent.

  4. For each of the remaining fields, prefix the XPath with Address/, so that every delivery address gets its own Address node:

    Field Name XPath
    Description Address/Name
    PostalName Address/PostalName
    AddressLine1 Address/Address1
    AddressLine2 Address/Address2
    AddressLine3 Address/Address3
    AddressLine4 Address/Address4
    PostCode Address/PostCode
    TaxNo Address/TaxNo

    The transaction names the container, DeliveryAddresses, and the fields name the repeating node inside it. People often get this pair the wrong way round. The Writing XML Documents cookbook article explains it in full.

  5. The resulting Field Mapping grid will look like this:

  6. Press Refresh again, now that the child is mapped.

  7. Open the file at the path and file name set on the Setup tab. It has delivery address nodes for the customers that have them, and none for the customers that do not:

Appendix 2 >