Mapping Primer

Before explaining the steps involved in the usage of the various APIs, you need to know what the default mapping is from SQL to XML (Generating XML) and back (Storing XML). As explained earlier, XML-SQL Utility (XSU) provides a canonical mapping from SQL data to XML data and back. The following mappings are explained below:

• Mapping: Generating XML from SQL

• Mapping: Storing SQL from XML

Mapping: Generating XML from SQL

Given a SQL query, a canonical mapping is made to create an XML document. Consider a table, emp  in the scott schema which has the structure,

CREATE TABLE emp 
(
   EMPNO NUMBER, 
   ENAME VARCHAR2(20),
   JOB VARCHAR2(20),
   MGR  NUMBER,
   HIREDATE DATE,
   SAL NUMBER,
   DEPTNO NUMBER
);

To convert the table elements to XML, we can fire off a query through one of the APIs available from the utility,

 select * from scott.emp;

On executing this query, the utility will generate an XML document which contains a ROWSET tag to enclose the results of all the rows. Each ROW is encapsulated within a ROW tag. The ROW tag also contains an attribute "num" which identifies the row number for each element. Each scalar element maps to an XML element. Column names become the tag names for the element. In the present case, any column which cannot be a valid XML identifier name (such as empno$ or empno#) has to be changed to a valid XML name by supplying an alias in the select query:

 <?xml version='1.0'?>
<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

The default mapping can be changed by using the various options through the APIs. Scalar values map to flat XML documents.

Oracle8i supports the notion of object types, collections and object references. These provide structural modelling within the server. The mapping to XML for these preserve the structure. For example, consider department table which contains a department address structure and a list of employees.

The AddressType is an object type which defines the structure of an address object.

CREATE TYPE AddressType AS OBJECT (
   STREET VARCHAR2(20),
   CITY   VARCHAR2(20),
   STATE  CHAR(2),
   ZIP    VARCHAR2(10)
);
/

An employee type is also present which defines the structure of an employee. Note how the employee's address is defined using the address type.

CREATE TYPE EmployeeType AS OBJECT
(
  EMPNO NUMBER,
  ENAME VARCHAR2(20),
  SALARY NUMBER,
  EMPADDR AddressType
);
/

Now, we can create a list of employees by defining a list type.

CREATE TYPE EmployeeListType AS TABLE OF EmployeeType;
/

And finally, the department table is created with a department address and the list of employees. Each row of this table contains a nested collection of employee objects each of which contains the employee's descriptions including their name, salary and address.

CREATE TABLE Dept 
(
  DEPTNO NUMBER,
  DEPTNAME VARCHAR2(20),
  DEPTADDR AddressType,
  EMPLIST  EmployeeListType
);

Assuming that valid values are stored in the department table, we can map the results of a select query on the table into an XML document shown below:-

<?xml version='1.0'?>
<ROWSET>
	 	 <ROW num="1">
 	 	  <DEPTNO>100</DEPTNO>
    <DEPTNAME>Sports</DEPTNAME>
    <DEPTADDR>
      <STREET>100 Redwood Shores Pkwy</STREET>
      <CITY>Redwood Shores</CITY>
      <STATE>CA</STATE>
      <ZIP>94065</ZIP>
    </DEPTADDR>
    <EMPLIST>
      <EMPLOYEE_TYPE num="1">
         <EMPNO>7369</EMPNO>
         <ENAME>John</ENAME>
         <SALARY>10000</SALARY>
         <EMPADDR>
           <STREET>300 Embarcadero</STREET>
           <CITY>Palo Alto</CITY>
           <STATE>CA</STATE>
           <ZIP>94056</ZIP>
         </EMPADDR>
      </EMPLOYEE_TYPE>
       <!-- additional employee types within the employee list -->
    </EMPLIST>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

You can see from the example above, how the object type attributes map to nested elements in the XML document and collection types map to XML lists. The same nesting can also be achieved by using CURSOR subqueries. With the use of object views you can realize the same structures from existing relational tables.

Mapping: Storing SQL from XML

The XML-SQL utility provides the ability to map the XML documents to table rows and also provides the ability to update top level columns and to delete rows. The storage uses a simple mapping to map the element tag names to columns. XML strings are converted to the appropriate data types through default mappings. If the XML element is structured, you can map it to a SQL object type.

Inserts

For example, the DEPTADDR element would get mapped to the AddressType SQL type when inserting into the Dept table. When mapping to object types and collections, only the structure and the names need to match. So one can generate the XML from a column of the AddressType2 and map it to a column of type AddressType provided the structure of the Address element is the same for both the types and the SQL type attribute names also match.

The insert case is handled simply by firing an insert statement and binding all the values of the elements in the VALUES clause of the insert statement. We would map the contents of each ROW element as a separate set of values to be inserted. So, if you take the XML document shown in the previous section, and ask XSU to insert it into the Dept table, the XSU will generate an insert statement of the form:

INSERT INTO Dept (DEPTNO, DEPTNAME, DEPTADDR, EMPLIST) VALUES (?,?,?,?)

and bind the values ,

DEPTNO <- 100
DEPTNAME <- SPORTS
DEPTADDR <- AddressType('100 Redwood Shores Pkwy','Redwood Shores',
                        'CA','94065')

EMPLIST <- EmployeeListType(EmployeeType(7369,'John',100000,
            AddressType('300 Embarcadero','Palo Alto','CA','94056'),...)

If there is more than one ROW element in the XML document, then for each ROW, the XSU bind the values and executes the statement. The insert processing can be optimized to insert in batches, commit in batches all of which is explained in the "Insert Processing" .

Updates

Updates and deletes differ from insert in that they can affect more than one row in the database table. In the case of insert, each ROW element of the XML document can affect at most one row in the table, provided that there are no triggers or constraints on the table. However, in the case of updates and deletes, the XML element might match more than one row if the matching columns are not key columns in the table.

In case of updates, the user is expected to provide a list of key columns which the XSU will use to identify the row to update. For example, to update the DEPTNAME to SportsDept instead of Sports, you can have an XML document such as,

<ROWSET>
	 	 <ROW num="1">
 	 	  <DEPTNO>100</DEPTNO>
    <DEPTNAME>SportsDept</DEPTNAME>
  </ROW>
</ROWSET>

and supply the DEPTNO as the key column. This would fire off the following update statement:

UPDATE DEPT SET DEPTNAME = ? WHERE DEPTNO = ? 

and bind the values ,

DEPTNO <- 100
DEPTNAME <- SportsDept

In the update case, you can also opt to update only a set of columns and not all the elements present in the XML document.

Deletes

In the case of deletes, the user can opt to give a set of key columns for the delete to identify the rows. If the set of key columns are not given, then the delete statement will try to match all the columns given in the document. Given a document such as:

<ROWSET>
	 	 <ROW num="1">
 	 	  <DEPTNO>100</DEPTNO>
    <DEPTNAME>Sports</DEPTNAME>
    <DEPTADDR>
      <STREET>100 Redwood Shores Pkwy</STREET>
      <CITY>Redwood Shores</CITY>
      <STATE>CA</STATE>
      <ZIP>94065</ZIP>
    </DEPTADDR>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

to delete, the utility will fire off a delete statement (one per ROW element) which would look like the following:

DELETE FROM Dept WHERE DEPTNO = ? AND DEPTNAME = ? AND DEPTADDR = ?
binding,
DEPTNO <- 100
DEPTNAME <- Sports
DEPTADDR <-  AddressType('100 Redwood Shores Pkwy','Redwood 
City','CA','94065')

Exact usage of all of these is explained in the "Basic Usage and advanced usage" sections. (**** cross reference this properly)