Setting up a sample project

This section guides you through setting up the XML via SQL Data Provider component using a simple example. To follow this example, use the PRODUCTS.xml as the XML source; it is located in the Demodata subdirectory of the Sybase ETL installation directory.

XML Port Manager

Open XML port manager, and define the ports in the OUT-port area. Each port is described by a select statement based on the XML Data Model tables.

XML source

The following XML document is a simple product structure. Each product is described with an ID (PR_ID), name (PR_NAME), product group (PR_GROUP1), and 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>

The data model

There is one table for the root element (TAB_dataroot), followed by one or more tables for elements at level 1. In the example, only one element at this level exists (TAB_PRODUCTS). At 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, and each level creates another set of tables.

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 through the ATT_FK_dataroot attribute.

The tables at level 2 are linked to table PRODUCTS through the ATT_FK_PRODUCTS attribute. To create the view containing the PRODUCTS records, the tables at level 2 must 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