This section guides you through the three steps of the setup process for the component using a simple example.
To follow the example, use the PRODUCTS.xml file as the XML source. It is located in the Demodata subdirectory of the Sybase IQ ETL installation directory.
The following XML document is a simple product structure. Each product is described with an ID (PR_ID), a name (PR_NAME), a product group (PR_GROUP1), and a price (PR_PRICE), for example:
<?xml version="1.0" encoding="UTF-8"?> <dataroot xmlns:od="urn:schemas-solonde-com:demodata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PRODUCTS.xsd" generated="2005-01-24T16:13:26"> <PRODUCTS> <PR_ID>435672</PR_ID> <PR_NAME>24 CD Rom Drive</PR_NAME> <PR_GROUP1>CD Rom</PR_GROUP1> <PR_PRICE>134</PR_PRICE> </PRODUCTS> <PRODUCTS> <PR_ID>435673</PR_ID> <PR_NAME>Notebook 235</PR_NAME> <PR_GROUP1>Notebook</PR_GROUP1> <PR_PRICE>1455</PR_PRICE> </PRODUCTS> </dataroot>
Click the XML button of the Data Output property to open the XML Port Manager.
In the upper area of the window, you can view the source XML document. The left section of the lower window contains two tabs:
The Data Model tab displays the generated relational schema.
The Reference tab displays the available component variables.
The right section of the lower window is the port area in which one or more ports can be defined. Each port is described by a SELECT statement based on the XML Data Model tables.
There is one table for the root element (TAB_dataroot) followed by one or more tables for elements on level 1. In the example, only one element on this level exists (TAB_PRODUCTS). On the next level, you find a table for each element on level 2 (TAB_PR_ID, TAB_PR_NAME, TAB_PR_GROUP1, TAB_PR_PRICE). There can be more nested levels in the XML document, each level will create another set of table.
You can change the prefixes for the generated table
names in the DB Schema Options property.
Root Level |
Elements Level 1 |
Elements Level 2 |
TAB_dataroot ATT_ROW_ID ATT_FK_generated ATT_xmlns_od ATT_xsi_no |
TAB_PRODUCTS ATT_ROW_ID ATT_FK_dataroot |
TAB_PR_ID ATT_ROW_ID ATT_FK_PRODUCTS ATT_PR_ID TAB_PR_NAME ATT_ROW_ID ATT_FK_PRODUCTS ATT_PR_NAME TAB_PR_GROUP1 ATT_ROW_ID ATT_FK_PRODUCTS ATT_PR_GROUP1 TAB_PR_PRICE ATT_ROW_ID ATT_FK_PRODUCTS ATT_PR_PRICE |
The tables are linked through foreign keys. Table TAB_PRODUCTS is linked to TAB_dataroot via attribute ATT_FK_dataroot. The tables on level 2 are linked to table PRODUCTS via attribute ATT_FK_PRODUCTS. To create the view containing the PRODUCTS records, the tables on Level 2 have to be joined with the TAB_PRODUCTS table. The join is qualified by the ATT_FK_PRODUCTS attribute of each Level 2 table and the ATT_ROW_ID of TAB_PRODUCTS. The only selected attributes are the value attributes of Level 2 tables: ATT_PR_ID, ATT_PR_NAME, ATT_PR_GROUP1 and ATT_PR_PRICE.
SELECT TAB_PR_ID.ATT_PR_ID, TAB_PR_NAME.ATT_PR_NAME, TAB_PR_GROUP1.ATT_PR_GROUP1, TAB_PR_PRICE.ATT_PR_PRICE FROM TAB_PRODUCTS, TAB_PR_ID, TAB_PR_NAME, TAB_PR_GROUP1, TAB_PR_PRICE WHERE TAB_PR_ID.ATT_FK_PRODUCTS = TAB_PRODUCTS.ATT_ROW_ID AND TAB_PR_NAME.ATT_FK_PRODUCTS = TAB_PRODUCTS.ATT_ROW_ID AND TAB_PR_GROUP1.ATT_FK_PRODUCTS = TAB_PRODUCTS.ATT_ROW_ID AND TAB_PR_PRICE.ATT_FK_PRODUCTS = TAB_PRODUCTS.ATT_ROW_ID
You can enter a SELECT statement for the port straight into the port field, or you can open the Query Designer by clicking Query Designer.
To control the automatic join generation (enable/disable), go
the File | Preferences menu.
Right-click on the port section and select Add port or Remove port.
An Info Port can be added to forward the XML document to the next component. This port is visible after exiting the XML Port Manager.
There is no impact on the simulation sequence.
For more information, see the XML via SQL Customer Sales Transfer Projects in the Demo Repository and Help Flash movie.
