Skyhawk Systems - Simplify data transformations
Products | Download | Buy | About Us
 
Table of 
   Contents Connect XML-2-DB User's Guide dbConfig.htmTable of ContentsmapRoot.htm

5. Mapping File

A mapping file defines the rules that map the XML element and attribute data to database tables and columns. It also defined the rules that map XML element and attribute date to parameters of procedures. A mapping file is also an XML file. The DTD for the mapping file is provided in the section DTD for mapping file. There is a DTD for SQL Server and another very similar one for Oracle.

The steps for creating a mapping file are listed below:

  1. Create a  <map-rules> element which is the root element for the mapping file. Specify the root element of the XML data file in the attribute, 'root-element' of the <map-rules> element.
  2. Create <table-reference-information> elements for all the tables into which data has to be inserted from the XML data file. Here you specify the owner, database name (for SQL Server) and the table name.
  3. Create <procedure-reference-information> elements for all the procedures that have to be executed for the given type of XML data file. Here you specify the owner, database name (for SQL Server), package name (for Oracle) and the procedure name.
  4. For Oracle databases, create <sequence-reference-information> elements for all the sequences that will be used to populate columns. Here you specify the owner and the sequence name.
  5. Create <insertion-map> elements for each of the tables and procedures in steps 1 and 2. This insertion map defines which XML element in the data file logically corresponds to each table or procedure. A record is inserted into a table at the end of each occurence of it's logically equivalent XML element in the data file. Similarly, a procedure is executed at the end of each occurence of it's logically equivalent XML element in the data file.
  6. Create <map> elements for all the tables and procedures in steps 1 and 2. The map element determines which XML element or attribute data is stored in which database column. Similarly for procedures, this map element can define which XML element or attribute data is used as the values for parameters for procedures that are executed.
  7. Create <map-const> elements. This lets you specify the constant value that has to be stored in some database column. Similarly, for procedures, this rule lets you specify the constant value that is passed in as the value of a parameter to a procedure.
  8. Create <map-param-const> elements. Instead of hard-coding the constant value in the mapping file, a different constant value can be passed in as a parameter to the Connect XML-2-DB program, every time it is executed. This rule lets you specify which database column to store this value in. Similarly, for procedures you can specify which parameter will get this value.
  9. For Oracle, create <map-seq> elements. If the next value of a sequence has to be stored in some database column, this rule lets you specify which sequence to use and which database column this value should be stored in.
  10. Create <db-propagation-rules> elements. If data is being inserted into multiple tables and there is a parent-child relationship between these tables, then this rule lets you specify which of the parent's columns should be populated in which one of the child's columns. This is especially useful when the primary key column values are generated by the database system (for example identity columns in SQL Server) and are not populated from the XML data file.
  11. Create <xml-propagation-rules> elements. If an XML element is defined as the logical equivalent of a table, in an insertion-map rule, then all the data that is mapped to this table's columns has to be either in that element's pcdata and attributes or in it's sub-elements' pcdata and attributes. None of the element's super-elements' data can be used directly. The xml-propagation-rule lets you propagate the data of an element to it's sub-elements. A more detailed example that clarifies this is provided in a later section.
  12. For SQL Server, create <map-identity> elements . If data has to be inserted into an identity column of a SQL Server table, then this rule should be defined. It lets you turn on the identity-insert before inserting a record into this table.

Mapping File

Copyright Skyhawk Systems. All Rights Reserved.
Send comments and questions to support@skyhawksystems.com.
dbConfig.htmTable of ContentsmapRoot.htm