5(e). Map Rule - <map>

• Inserting XML element data into a column of a database table
• Inserting XML attribute data into a column of a database table
• Passing XML element data as the value of a parameter to a procedure
• Passing XML attribute data as the value of a parameter to a procedure

Once all the table-references, procedure-references and sequence-references (only for Oracle) have been defined, mapping rules must be defined. For every column of a table that you want to insert data into, a mapping rule must be defined. This rule determines which XML element or attribute is to be stored in that database column. For each procedure that you want to invoke, mapping rules must be defined. These rules determine which XML elements or attributes have to be passed in as the parameters to the procedure. There are several types of mapping rules - <map>, <map-const>, <map-param-const>, <map-identity> (only for SQL Server), <map-seq> (only for Oracle).

The format of the <map> element for SQL Server and Oracle databases is as follows:

      <!ELEMENT map                   EMPTY>
      <!ATTLIST map
          element-name           CDATA #REQUIRED
          attribute-name         CDATA #IMPLIED
          use-pcdata             (yes | no) "no"
          table-reference        CDATA #IMPLIED
          proc-reference         CDATA #IMPLIED
          column-name            CDATA #IMPLIED
          param-name             CDATA #IMPLIED
          source-datetime-format CDATA #IMPLIED>
	

The <map> element can be used to

• map an element's pcdata to a database column
  • In this case, the attributes element-name, table-reference, column-name and use-pcdata must be provided. Also, use-pcdata must be set to "yes".
  • In the case of Oracle databases, the column-name values must be in uppercase, unless the column was created with quotes around the name, in which mixed case should be used.
  • In the case of SQL Server databases, the column-name values are case-sensitive. You must use the same case that was used when creating the table.
• map an element's attribute data to a database column
  • In this case, the attributes element-name, table-reference, column-name and attribute-name must be provided. Also, use-pcdata should not be set.
  • In the case of Oracle databases, the column-name value must be in uppercase, unless the column was created with quotes around the name, in which mixed case should be used.
  • In the case of SQL Server databases, the column-name values are case-sensitive. You must use the same case that was used when creating the table.
• map an element's data to a parameter of a procedure invocation
  • In this case, the attributes element-name, proc-reference, param-name and use-pcdata must be provided. Also, use-pcdata must be set to "yes".
  • In the case of Oracle databases, the param-name value must be in uppercase.
  • In the case of SQL Server databases, the param-name values are case-sensitive. You must use the same case that was used when creating the procedure.
  • In the case of SQL Server databases, the param-name value must be prefixed with &.
• map an element's attribute data to a parameter of a procedure invocation
  • In this case, the attributes element-name, proc-reference, param-name and attribute-name must be provided. Also, use-pcdata should not be set.
  • In the case of Oracle databases, the param-name value must be in uppercase.
  • In the case of SQL Server databases, the param-name values are case-sensitive. You must use the same case that was used when creating the procedure.
  • In the case of SQL Server databases, the param-name value must be in prefixed with &.

5(ei). Inserting XML element data into a column of a database table.

(a) For example, suppose that we have an xml data file that looks like the following:

      <address>
         <street>8896 BlueBird Drive</street>
         <city>Santa Monica</city>
         <state>CA</state>
         <zipcode>98859</zipcode>
      </address>
	  

(b) Also, assume that the SQLServer table Billing.TESTUSER.addresses has a reference, BillingAddresses, defined for it in the mapping file.

    <table-reference-information
         table-reference="BillingAddresses"
         table-name="addresses"
         database-name="Billing"
         object-owner="TESTUSER">
    </table-reference-information>
   

(c) Assume that the XML element street's data, "8896 BlueBird Drive", has to be stored in the column, STREET_NAME_1, of this table.

Then a mapping rule to to do this would be as follows:

  
      <map
          element-name="street" 
          use-pcdata="yes"
          table-reference="BillingAddresses"
          column-name="STREET_NAME_1">
     </map>
	 

5(eii). Inserting XML attribute data into a column of a database table.

(a) For example, suppose that we have an xml data file that looks like the following:

      <BillingInfo>
          <address street="8896 BlueBird Drive" city="Santa Monica" state="CA" zipcode="98859"/>
      </BillingInfo>

(b) Also, assume that the SQLServer table Billing.TESTUSER.addresses has a reference, BillingAddresses, defined for it in the mapping file.

      <table-reference-information
           table-reference="BillingAddresses"
           table-name="addresses"
           database-name="Billing"
           object-owner="TESTUSER">
      </table-reference-information>
	  

(c) Assume that for the XML element, address, the value of it's street attribute ("8896 BlueBird Drive") has to be stored in the column, STREET_NAME_1 of the Billing.TESTUSER.addresses table.

Then a mapping rule to to do this would be as follows:

      <map
          element-name="address" 
          attribute-name="street"
          table-reference="BillingAddresses"
          column-name="STREET_NAME_1">
      </map>
	 

5(eiii). Passing XML element data as the value of a parameter to a procedure.

SQL Server Example

(a) For example, suppose that we have an xml data file that looks like the following:

      <address>
         <street>8896 BlueBird Drive</street>
         <city>Santa Monica</city>
         <state>CA</state>
         <zipcode>98859</zipcode>
      </address>
	

(b) Also, assume that the SQLServer procedure Billing.TESTUSER.AddAddress has a reference, AddBillingAddress, defined for it in the mapping file.

      <procedure-reference-information
           proc-reference="AddBillingAddress"
           proc-name="AddAddress"
           database-name="Billing"
           object-owner="TESTUSER">
      </procedure-reference-information>
	  

(c) Assume that the XML element street's data, "8896 BlueBird Drive", has to be passed in as the value for the i_street_name parameter for the procedure Billing.TESTUSER.AddAddress.

Then a mapping rule to to do this would be as follows:(please note that for SQL Server the param name value is prefixed with a @)

      <!--  SQL Server example -->
      <map
          element-name="street" 
          use-pcdata="yes"
          proc-reference="AddBillingAddress"
          param-name="@i_street_name">
      </map>
	  

Oracle Example

(a) For example, suppose that we have an xml data file that looks like the following:

      <address>
         <street>8896 BlueBird Drive</street>
         <city>Santa Monica</city>
         <state>CA</state>
         <zipcode>98859</zipcode>
      </address>
    

(b) Also, assume that the Oracle procedure ADDADDDRESS in the package BILLING_PACKAGE in the schema TESTUSER, has a reference, AddBillingAddress, defined for it in the mapping file.

      <procedure-reference-information
           proc-reference="AddBillingAddress"
           proc-name="ADDADDRESS"
           package-name="BILLING_PACKAGE"
           object-owner="TESTUSER">
      </procedure-reference-information>
	  

(c) Assume that the XML element street's data, "8896 BlueBird Drive", has to be passed in as the value for the I_STREET_NAME parameter for the procedure Billing.TESTUSER.AddAddress. This procedure will be validating the data and then be inserting the data into the Billing.TESTUSER.addresses tables.

Then a mapping rule to to do this would be as follows (please note that for Oracle the param name value has to be in uppercase and it is not prefixed with a @, as it is with SQL Server):

      <!--  Oracle example -->
      <map
         element-name="street" 
         use-pcdata="yes"
         proc-reference="AddBillingAddress"
         param-name="I_STREET_NAME">
      </map>
	  

5(eiv). Passing XML attribute data as the value of a parameter to a procedure.

(a) For example, suppose that we have an xml data file that looks like the following:

      <BillingInfo>
          <address street="8896 BlueBird Drive" city="Santa Monica" state="CA" zipcode="98859"/>
      </BillingInfo>

(b) Also, assume that the SQLServer procedure Billing.TESTUSER.AddAddress has a reference, AddBillingAddress, defined for it in the mapping file.

      <procedure-reference-information
         proc-reference="AddBillingAddress"
         proc-name="AddAddress"
         database-name="Billing"
         object-owner="TESTUSER">
      </procedure-reference-information>
	  

(c) Assume that the value of the attribute street ("8896 BlueBird Drive") of the element address, has to be passed in as the value for the I_STREET_NAME parameter for the procedure Billing.TESTUSER.AddAddress.

Then a mapping rule to to do this would be as follows: (please note that for SQL Server the param name value is prefixed with a @)

      <!--  SQL Server example -->
      <map 
         element-name="address" 
         attribute-name="street"
         proc-reference="AddBillingAddress"
         param-name="@i_street_name">
      </map>