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
