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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
|