Skip to content

Item & Price Export / Extract

Sage 200 Item & Price Extract (SAMS200EXTRT)

This exports item and pricing data from Sage 200 to an XML file. An e-commerce store needs this master data to know what it can sell and at what price. There is a Sage 300 equivalent built the same way.

The extract reads the Sage 200 database directly, with the database reader, because IMan has no native read from Sage 200.

  1. Database Read
  2. Map
  3. Write

Database Reader

The database connection has to exist before the extract can run

The database connection must exist before the extract can run. You set it up once, under Setup > Database Connections, and every integration that uses it shares it. See Database Settings.

The reader names that shared connection instead of holding its own connection string. It also holds the SELECT statement it runs:

The database reader's Setup tab: Database Connection set to Sage200 DemoData Connection, and an SQL Statement selecting item code, name, base unit, commodity code, product group, comments, part number and price from StockItem joined to ProductGroup, StockItemPrice and PriceBand where the price band name is Standard

The query joins the stock item to its product group and to the Standard price band. It names each column after the field it becomes, for example I.Code as 'ItemNumber' and P.Price as 'BasePrice'.

Press Refresh to test it. The rows appear in the Preview area. If an error appears instead, check the connection first, under Setup > Database Connections.

The first open can take ten seconds or more

Opening a database reader for the first time can take ten seconds or more, while IMan finds the server named in the connection.

Map

The Map transform does two small jobs here:

  • It rounds BasePrice and formats it to two decimal places with Format(Round(%BasePrice, 2), "0.00"), so the file carries 163.20 and not the database's full precision.
  • It adds RunDate, Format(Now, "yyyyMMddThh:mm:ss.000Z"), which records when the file was produced. A system that imports the file nightly can use it to tell one extract from the next.

Write (XML Writer)

The writer turns the flat result set into XML. You give each field its path in the document and, optionally, the name of an attribute to write it as instead of an element:

The XML writer's Field Mapping tab: a grid of Field Name, Type, Export, XPath, Relative and Attribute, mapping ItemNumber to SKU, Name to ProductName, BaseUnitName to UnitOfMeasure, BasePrice to Price/Base, Category to the Attributes element as the Category attribute, the two comment fields to Attributes/ShortDescription and Attributes/LongDescription, and RunDate to Items as the ExportDateTime attribute with Relative unticked

Three columns control the output:

  • XPath is the path within the record. A plain name like SKU is an element directly beneath the item. Price/Base also creates the intermediate Price element.
  • Attribute, where it is filled in, writes the value as an attribute of the element the XPath names, not as the element's content. Category becomes <Attributes Category="CABINETS">.
  • Relative separates a field of the record from a field of the document. RunDate is the only row with it unticked, so IMan writes its ExportDateTime attribute once, on the root Items element, and not on every item.

Press Refresh to generate the file in the folder named by File Path on the Setup tab:

<?xml version="1.0" encoding="utf-8"?>
<Items ExportDateTime="20260901T09:20:32.000Z">
  <Item>
    <SKU>CA/BASE/SNG/BEECH</SKU>
    <ProductName>Beech Base Single Cabinet H58cm</ProductName>
    <UnitOfMeasure>Each</UnitOfMeasure>
    <CommodityCode>94034010</CommodityCode>
    <Price>
      <Base>163.20</Base>
    </Price>
    <Attributes Category="CABINETS">
      <ShortDescription><![CDATA[]]></ShortDescription>
      <LongDescription><![CDATA[]]></LongDescription>
      <SupplierCode></SupplierCode>
    </Attributes>
  </Item>
</Items>

To understand XPath mapping, compare the file with the grid. Every element name in the file appears in the XPath column, and the attribute on Items comes from the one row whose Relative box is unticked.