Magic Software
Lesson 7Hands-on · ~30 min

Checking Customer Existence

A central part of any integration project is data transformation. In Magic xpi the Data Mapper is the workhorse for that job. In this lesson you'll meet the Data Mapper, use it to extract details from a request XML, and check whether the customer exists in the local database.

What the Data Mapper Does

Think of the Data Mapper as a "Read/Write" tool: it reads from one or more sources and writes to one or more destinations. Use it to:

  • Create or update files in XML, HTML, or flat-file formats.
  • Create, update, and delete database records.
  • Call a flow and pass it variables.

Source data can be:

  • XML, JSON, or a flat file (text or CSV).
  • A database SELECT statement.
  • Variables, ODS, or UDS entries.

Destination data can be any of the same types, plus:

  • Database INSERT, UPDATE, or DELETE statements.
  • A flow invocation (Call Flow).
  • A template (HTML or RTF).
One destination minimum. A Data Mapper step must have at least one destination object — otherwise it has nothing to write to.

Extracting Details from the Request

The previous lesson moved request files into a working directory. Now you'll pull the customer information out of that XML and into flow variables so later steps can use it.

  1. Drop a Data Mapper utility as a child step of Send Email to Sales. Name the step Extract Details from Request.
  2. Open its configuration. Drag an XML source into the Source Tree.
  3. Right-click the source and choose Show Properties. Set Name to Request.
  4. Set XSD File to %currentprojectdir%\course_data\schemas\request.xsd.
  5. Set Source Type to Variable and pick F.RequestXML.

For the destination, drag a Variable destination, name it Variables, and select these flow variables:

  • F.ContactName
  • F.CustomerEmail
  • F.CustomerId

Key XML Source Properties

Name / DescriptionIdentification fields for the source.
XSD FilePath to the XML schema. Required — you must give the exact file name.
XML RootPick the root element when more than one is defined in the XSD; defaults to the first.
XSD Sub TypeFile, IFC Model, or XML Position Forwarding (source only).
Source TypeFile (external XML on disk) or Variable (BLOB).
XML ValidationValidates the XML against the schema before mapping — produces an error if invalid.
Recursion DepthHow many times the structure repeats; leave at 1 when it doesn't recurse.
Use Streaming ParserLets the Data Mapper handle very large XML documents (source only).

Mapping Source to Destination

The last step is to map the request XML into the variables:

  1. Expand the source: Request » Request » CustomerDetails.
  2. Expand the destination: Variables » Instance.
  3. Right-click Customer_ID in the source and choose Connect; drop the line on F.CustomerId.
  4. Expand ContactDetail and connect ContactName to F.ContactName and Contact_email to F.CustomerEmail.

A single white line shows a single mapping. A double white line on a parent compound means "loop — for each occurrence." A blue line marks the connection currently in focus.

About loops. If three CustomerID elements were in the XML, Magic xpi would assign each in turn to F.CustomerId — only the last value survives in a scalar variable.

Set Auto Start to Yes on the Scan for New Requests flow, set a breakpoint on the new step, and run the Debugger to confirm the variables are populated correctly.

Checking Whether the Customer Exists

Now that you have the customer ID, the next question is whether that customer exists in the local database. A database object can act as a Data Mapper source or destination, but only certain operations are valid in each role:

SourceSELECT only.
DestinationINSERT, UPDATE, or DELETE.
  1. Drop a Data Mapper as a child of Extract Details from Request. Name it Check if the Customer Exists.
  2. Drag a Database source into the Source Tree. Set its Name to CheckCustomer.
  3. Click the Wizard button next to the SQL Statement field. The Database Wizard opens.
  4. Add the Customers table and select the CustomerName column.
  5. In the Where Clause step, build: [Customers].CustomerID = <?F.CustomerId?>. The <?...?> markers tell Magic xpi to substitute the variable's value at runtime.
  6. Skip the ORDER BY step (a single result is expected) and click Finish.

Wiring Up the Result

  1. Drag a Variable destination, name it CustomerName, and select F.CustomerName.
  2. Expand the source and destination.
  3. Connect CustomerName in the source to F.CustomerName in the destination.
What if no record is returned? If the customer doesn't exist, the SELECT returns no rows and F.CustomerName remains blank. You'll branch on that fact later in the course.

Exercise

Practice these. The following steps run only when the customer is found:
  1. Pull the Contact ID from the request XML. Add a flow variable to hold it.
  2. Use the Contact ID to look up the Contacts table and check whether the contact exists.
  3. If the contact does not exist, send the customer an email saying: "You are not registered as an official contact for your company. Please approach your representative."

Summary

You should now be able to:

  • Add a Data Mapper step to a flow and configure its source and destination.
  • Use the Data Mapper to extract values from an XML payload into variables.
  • Use the Data Mapper to fetch a row from a database and write it into a variable.
  • Use the <?Variable?> notation in SQL WHERE clauses.