Skip to content

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

Estimated time

Estimated time: 1 Hr

In this exercise you configure an export. It queries the Sage 300 database directly for customers and their ship-to locations, 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 300 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.IDCUST, A.NAMECUST, A.TEXTSTRE1, A.TEXTSTRE2, A.TEXTSTRE3,
       A.TEXTSTRE4, A.NAMECITY, A.CODESTTE, A.CODEPSTL, A.CODECTRY,
       A.NAMECTAC, P.IDCUSTSHPT, P.NAMELOCN, P.TEXTSTRE1 as SHIPADDR1,
       P.TEXTSTRE2 as SHIPADDR2, P.TEXTSTRE3 as SHIPADDR3,
       P.TEXTSTRE4 as SHIPADDR4, P.NAMECITY as SHIPCITY,
       P.CODESTTE as SHIPSTTE, P.CODEPSTL as SHIPPSTL,
       P.CODECTRY as SHIPCTRY
from ARCUS A left outer join ARCSP P on A.IDCUST = P.IDCUST

    The join is an outer join, so the query still returns a customer with no ship-to location. The Filter removes the empty ship-to records 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.

  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 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
    IDCUST Key field 1
    Ship-to fields Deselect all

  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 ShipLocation.

  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
    IDCUST Select, key field 1
    Ship-to fields Select all
    IDCUSTSHPT 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 ship-to location. This is expected, because:

    • the SQL query uses a left outer join
    • where there are no ship-to records, the query still returns the customer record, with null or empty ship-to 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 the ShipLocation transaction.

  3. Enter the filter:

    %IDCUSTSHPT <> ""

  4. Click outside the editor to commit the filter, then press Refresh. The empty rows are gone.

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 IDCUST 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
    IDCUST CustomerId
    NAMECUST Name
    TEXTSTRE1 Address1
    TEXTSTRE2 Address2
    TEXTSTRE3 Address3
    TEXTSTRE4 Address4
    NAMECITY City
    CODESTTE State
    CODEPSTL PostCode
    CODECTRY Country
    NAMECTAC Contact

  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 ShipLocation from the Current Transaction Id drop-down.

  2. Set the Transaction Type XPath to ShipLocations.

  3. Edit the IDCUST 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 ShipLocation/, so that every ship-to location gets its own ShipLocation node:

    Field Name XPath
    IDCUSTSHPT ShipLocation/LocationId
    NAMELOCN ShipLocation/LocationName
    SHIPADDR1 ShipLocation/Address1
    SHIPADDR2 ShipLocation/Address2
    SHIPADDR3 ShipLocation/Address3
    SHIPADDR4 ShipLocation/Address4
    SHIPCITY ShipLocation/City
    SHIPSTTE ShipLocation/State
    SHIPPSTL ShipLocation/PostCode
    SHIPCTRY ShipLocation/Country

    The transaction names the container, ShipLocations, 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 ship-to location nodes for the customers that have them, and none for the customers that do not:

Appendix 2 >