5(i). Map Identity Rule - <map-identity> (only for SQL Server)

Connect XML-2-DB supports inserting values into identity columns of SQL Server tables. If data from the XML file has to be inserted into the identity column of a table, then the <map-identity> lets you turn on identity inserts for that table and column. The format of this element is as follows:

     <!ELEMENT map-identity          EMPTY>
     <!ATTLIST map-identity
          element-name        CDATA #REQUIRED
          table-reference     CDATA #REQUIRED
          column-name         CDATA #REQUIRED
          set-identity-insert (on | off) >
	

The element-name is the element that is logically equivalent to this table. There should already be an insertion-map defined for this element-name and table-reference.

Suppose that we have the following XML data file.

	
<customer-purchases>
     <software-sale sale-id="1012" customer-first-name="Bill" customer-last-name="Smith" 
                          name="ABC Software" purchase-price="120.00" purchase-date="12/20/2001"
	             sales-agent="Patrick"/>
     <software-sale sale-id="1013" customer-first-name="John" customer-last-name="Bush" 
	             name="DZ Software" purchase-price="2000.00" purchase-date="10/10/2002"
	             sales-agent="Jane"/>
</customer-purchases>
	

Suppose that the SALES table, in a SQL Server database, has SALES_ID as an identity column. Assume that the value of the sale-id attribute of software-sale has to be inserted into the SALE_ID identity column of SALES. Then the following rules should be defined in the mapping file:

<!-- Define a table reference for SALES -->
<table-reference-information
       table-reference="Sales"
       table-name="SALES"
       object-owner="TESTUSER"
       database-name="Billing"/>
		  
<!-- Indicate that the software-sale elementis logically equivalent to the SALES table -->
<!-- insertion-map described in a later section  -->
<insertion-map element-name="software-sale" table-reference="Sales"/>
		  
<!-- Turn on the identity insert fo the SALES table and identity column SALE_ID -->
<map-identity 
    element-name="software-sale"
    table-reference="Sales"
    column-name="SALE_ID"
    set-identity-insert="on"/>
		  
<!-- Store the value of the attribute sale-id into the column SALE_ID -->
<map
    element-name="software-sale"
    attribute-name="sale-id"		  
    table-reference="Sales"
    column-name="SALE_ID"/>