5(m). Database Propagation Rule- <db-propagation-rule>

When inserting records into multiple tables where there is a master-detail relationship between the tables, we must be able to propagate any primary key column values from the master to the detail tables. Sometimes, these primary key values are generated at insert time by the database. This is the case with identity columns in SQL Server databases. In other cases, as with Oracle, a sequence is used to generate the next unique value for a primary key column, whose value has to be propagated to the foreign key columns in the detail table.

Connect XML-2-DB supports the propagation of a parent's database column values to a detail table's columns. This is done by specifying db-propagation-rules in the mapping file. This rule indicates which table is the parent/source table and which is the child/target table. It also specifies which columns in the parent table have to be propagated to the detail table's columns.

Similarly, Connect XML-2-DB supports propagating an output parameter of one procedure to an input parameter of another procedure. In addition to propagating values from table to table and from procedure to procedure, values can also be propagated between tables and procedures.

The format of a db-propagation-rule is as follows:

      <!ELEMENT db-propagation-rule        (link+)>
      <!ATTLIST db-propagation-rule
          parent-reference    CDATA #REQUIRED
          child-reference     CDATA #REQUIRED>

      <!ELEMENT link                       EMPTY>
      <!ATTLIST link
          parent-column-name         CDATA #IMPLIED
          parent-param-name          CDATA #IMPLIED
          use-parent-return-value    CDATA #IMPLIED
          child-column-name          CDATA #IMPLIED
          child-param-name           CDATA #IMPLIED
          source-datetime-format     CDATA #IMPLIED>

Suppose we have the following XML data file.

      <sales>
         <customer first-name="Bill" last-name="Smith">
	     <address street="123 Bird Ave." city="Austin" state="TX" zipcode="98070"/>
	     <software-sale name="Quicken" purchase-price="120.00"/>
	     <software-sale name="Turbo Tax" purchase-price="220.00"/>		
         </customer>
         <customer first-name="John" last-name="Bush">
	     <address street="123 Raven Dr." city="Dallas" state="TX" zipcode="99099"/>
	     <software-sale name="DZ Software" purchase-price="2000.00" purchase-date="10/10/2002"
	                     sales-agent="Jane"/>
	     <software-sale name="Quicken" purchase-price="150.00" purchase-date="10/12/2002"
	                     sales-agent="Jane"/>
	     <software-sale name="Turbo Tax" purchase-price="50.00" purchase-date="10/10/2002"
	                     sales-agent="Jane"/>
         </customer>		
      </sales>

Example: Parent/Source is a table. Child/target is a table.

Assume that records have to be inserted into the CUSTOMERS and SALES tables, where CUSTOMERS is a parent table of the SALES table. Assume that this is a SQL Server database and that the primary key, CUSTOMER_ID, of the CUSTOMERS table is an identity column and its value is generated by the database when a record is inserted into the CUSTOMERS table. Also, assume that this value has to be propagated to the CUSTOMER_ID column of the SALES table. The db-propagation-rule and the table-reference rules to do this are as follows:

      <table-reference-information table-reference="Customers" table-name="CUSTOMERS"/>

      <table-reference-information table-reference="Sales" table-name="SALES"/>
	  
      <db-propagation-rule parent-reference="Customers" child-reference="Sales">
         <link parent-column-name="CUSTOMER_ID" child-column-name="CUSTOMER_ID"/>
      </db-propagation-rule>

The tables CUSTOMERS and SALES do NOT have to have a parent-child relationship defined in the database for this db-propagation rule to work.

Example: Parent/Source is a procedure. Child/target is a procedure.

Assume that there are procedures ADD_CUSTOMER and ADD_SALE defined. ADD_CUSTOMER has an output parameter O_CUSTOMER_ID defined. ADD_SALE has an input parameter, I_CUSTOMER_ID defined. We want the value of O_CUSTOMER_ID to be propagated to I_CUSTOMER_ID. This is also done with a db-propagation-rule.

      <procedure-reference-information proc-reference="AddCustomer" proc-name="ADD_CUSTOMER"/>

      <procedure-reference-information proc-reference="AddSale" proc-name="ADD_SALE"/>
	  
      <db-propagation-rule parent-reference="AddCustomer" child-reference="AddSale">
         <link parent-param-name="O_CUSTOMER_ID" child-param-name="I_CUSTOMER_ID"/>
      </db-propagation-rule>

Example: The return value of the parent stored function has to be propagated to the child

Assume that a stored function, FN_ADD_CUSTOMER, has been defined, which returns a value. This return value has to be passed in as the value for the parameter I_CUSTOMER_ID of the ADD_SALE procedure. The corresponding db-propagation rule would be as follows:

      <procedure-reference-information proc-reference="FnAddCustomer" proc-name="FN_ADD_CUSTOMER"/>

      <procedure-reference-information proc-reference="AddSale" proc-name="ADD_SALE"/>
	  
      <db-propagation-rule parent-reference="FnAddCustomer" child-reference="AddSale">
         <link use-parent-return-value="yes" child-param-name="I_CUSTOMER_ID"/>
      </db-propagation-rule>

Example: Parent/Source is a procedure. Child/target is a table.

Assume that there the procedure ADD_CUSTOMER is defined. ADD_CUSTOMER has an output parameter O_CUSTOMER_ID defined. The value of O_CUSTOMER_ID has to be propagated to the CUSTOMER_ID column of the SALES table. The corresponding db-propagation rule would be as follows:

      <procedure-reference-information proc-reference="AddCustomer" proc-name="ADD_CUSTOMER"/>

      <table-reference-information table-reference="Sales" table-name="SALES"/>
	  
      <db-propagation-rule parent-reference="AddCustomer" child-reference="Sales">
         <link parent-param-name="O_CUSTOMER_ID" child-column-name="CUSTOMER_ID"/>
      </db-propagation-rule>