Updating with DataSet, OracleDataAdapter, and OracleCommandBuilder

If the XMLType column is fetched into the DataSet, the XMLType data is represented as a .NET String.

Modifying XMLType data in the DataSet does not require special treatment. XMLType data can be modified in the same way as any data that is stored in the DataSet. When a change is made and OracleDataAdapter.Update() is invoked, the OracleDataAdapter ensures that the XMLType data is handled properly. OracleDataAdapter uses any custom SQL INSERT, UPDATE, or DELETE statements that are provided. Otherwise, valid SQL statements are generated by the OracleCommandBuilder as needed to flush the changes to the database server.

Updating with OracleCommand and OracleParameter

OracleCommand provides a powerful way of updating XMLType data, especially with the use of OracleParameter. To update columns in a database table, the new value for the column can be passed as an input parameter of a command.

// Create a table with an XMLType column in the database
CREATE TABLE XMLTABLE(NUM_COL number, XMLTYPE_COL xmltype);

INSERT INTO XML_TABLE (NUM_COL, XMLTYPE_COL) VALUES (4, 
      XMLType.createxml('<DOC/>'));

Handling Date and Time Format

Table 3-19 lists the date and time format handling for different database releases.

Special Characters in Column Data

If any of the data in the select list columns in the query contain any characters with special meaning in XML (see Table 3-18), these characters are replaced with their corresponding entity encoding in the result XML document.

The following examples demonstrate how ODP.NET handles the angle bracket special characters in the column data:

// Create the following table
create table specialchars ("id" number, name varchar2(255));
insert into specialchars values (1, '<Jones>');

// Create the connection
string constr = "User Id=hr;Password=hr;Data Source=orcl";
OracleConnection conn = new OracleConnection(constr);
conn.Open();

// Create the command
OracleCommand cmd = new OracleCommand("", conn);

// Set the XML command type to query.
cmd.XmlCommandType = OracleXmlCommandType.Query;

// Set the SQL query
cmd.CommandText = "select * from specialchars";

// Set command properties that affect XML query behavior.
cmd.BindByName = true;

// Set the XML query properties
cmd.XmlQueryProperties.MaxRows = -1;

// Get the XML document as an XmlReader.
XmlReader xmlReader = cmd.ExecuteXmlReader();
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.PreserveWhitespace = true;
xmlDocument.Load(xmlReader);
Console.WriteLine(xmlDocument.OuterXml);

The following XML document is generated for that table:

<?xml version = '1.0'?>
<ROWSET>
   <ROW>
      <id>1</id >
      <NAME>&lt;Jones&gt;</NAME>
   </ROW>
</ROWSET>

Special Characters In Table or View Name

If a table or view name has any non-alphanumeric characters other than an underscore (_), the table or view name must be enclosed in quotation marks.

For example, to select all entries from a table with the name test'ing, the CommandText property of the OracleCommand object must be set to the following string.

"select * from \"test'ing\"";

Case-Sensitivity in Column Name to XML Element Name Mapping

The mapping of SQL identifiers (column names) to XML element names is case sensitive and the element names are in exactly the same case as the column names of the table or view.

However, the root tag and row tag names are case insensitive. The following example demonstrates case-sensitivity in this situation:

//Create the following table
create table casesensitive_table ("Id" number, NAME varchar2(255));
 
//insert name and id
insert into casesensitive_table values(1, 'Smith');
 
The following XML document is generated:
<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <Id>1</Id>
      <NAME>Smith</NAME>
    </ROW>
  </ROWSET>

Note that the element name for the Id column matches the case with the column name.

Column Name to XML Element Name Mapping

For each row generated by the SQL query, the SQL identifier (column name) maps to an XML element in the generated XML document.

The following example demonstrates this:

// Create the following table
create table emp_table (EMPLOYEE_ID NUMBER(4), LAST_NAME varchar2(25));
// Insert some data
insert into emp_table values(205, 'Higgins');

The SQL query, select * from emp_table, generates the following XML document:

<?XML version="1.0"?>
  <ROWSET>
    <ROW>
      <EMPLOYEE_ID>205</EMPLOYEE_ID>
      <LAST_NAME>Higgins</LAST_NAME>
    </ROW>
  </ROWSET>

The EMPLOYEE_ID and LAST_NAME database columns of the employees table map to the EMPLOYEE_ID and LAST_NAME elements of the generated XML document.

select "some id" as "some_x0020_id", name from specialchars;

select "some id", name from specialchars;

Object-Relational Data

ODP.NET can generate an XML document for data stored in object-relational columns, tables, and views.

The following example demonstrates this:

// Create the following tables and types
CREATE TYPE "EmployeeType" AS OBJECT (EMPNO NUMBER, ENAME VARCHAR2(20));
/
CREATE TYPE EmployeeListType AS TABLE OF "EmployeeType";
/
CREATE TABLE mydept (DEPTNO NUMBER, DEPTNAME VARCHAR2(20), 
              EMPLIST EmployeeListType)
              NESTED TABLE EMPLIST STORE AS EMPLIST_TABLE;
INSERT INTO mydept VALUES (1, 'depta',
              EmployeeListType("EmployeeType"(1, 'empa')));

The following XML document is generated for the table:

<?xml version = "1.0"?>
<ROWSET>
   <ROW>
      <DEPTNO>1</DEPTNO>
      <DEPTNAME>depta</DEPTNAME>
      <EMPLIST>
         <EmployeeType>
            <EMPNO>1</EMPNO>
            <ENAME>empa</ENAME>
         </EmployeeType>
      </EMPLIST>
   </ROW>
</ROWSET>

ODP.NET encloses each item in a collection element, with the database type name of the element in the collection. The mydept table has a collection in the EMPLIST database column and each item in the collection is of type EmployeeType. Therefore, in the XML document, each item in the collection is enclosed in the type name EmployeeType.

NULL values

If any database row has a column with a NULL value, then that column does not appear for that row in the generated XML document.

Handling of Date and Time Format

Table 3-20 lists the date and time format handling for different database releases.

Saving Changes Using XML

Changes can be saved to database tables and views using XML data. However, insert, update, and delete operations cannot be combined in a single XML document. ODP.NET cannot accept a single XML document and determine which changes are inserts, updates, or deletes.

The inserts must be in an XML document containing only rows to be inserted, the updates only with rows to be updated, and the deletes only with rows to be deleted.

For example, using the employees table that comes with the HR sample schema, you can specify the following query:

select employee_id, last_name from employees where employee_id = 205;

The following XML document is generated:

<?xml version = '1.0'?>
<ROWSET>
   <ROW>
      <EMPLOYEE_ID>205</EMPLOYEE_ID>
      <LAST_NAME>Higgins</LAST_NAME>
   </ROW>
</ROWSET>

To change the name of employee 205 from Higgins to Smith, specify the employees table and the XML data containing the changes as follows:

<?xml version = '1.0'?>
<ROWSET>
   <ROW>
      <EMPLOYEE_ID>205</EMPLOYEE_ID>
      <LAST_NAME>Smith</LAST_NAME>
   </ROW>
</ROWSET>

Special Characters in Column Data

If the data in any of the elements in the XML document contains characters that have a special meaning in XML (see Table 3-18), these characters must be entity- encoded or escaped in the XML document, so that the data is stored correctly in the database table column. Otherwise, ODP.NET throws an exception.

The following examples demonstrate how ODP.NET handles the angle bracket special characters in the column data.

// Create the following table
create table specialchars ("id" number, name varchar2(255));

The following XML document can be used to insert values (1, '<Jones>') to the specialchars table:

<?xml version = '1.0'?>
 <ROWSET>
  <ROW>
   <id>1</id >
   <NAME>&lt;Jones&gt;</NAME>
  </ROW>
 </ROWSET>

Special Characters in Table or View Name

If a table or view name has any non-alphanumeric characters other than an underscore (_), the table or view name must be enclosed in quotation marks.

For example, to save changes to a table with the name test'ing, the OracleCommand.XmlSaveProperties.TableName property must be set to "\"test'ing\"".

Case-Sensitivity in XML Element Name to Column Name Mapping

For each XML element representing a row of data in the XML document, the child XML elements map to database column names. The mapping of the child element name to the column name is always case sensitive, but the root tag and row tag names are case insensitive. The following example demonstrates this case-sensitivity:

//Create the following table
create table casesensitive_table ("Id" number, NAME varchar2(255));

The following XML document can be used to insert values (1, Smith) into the casesensitive_table:

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <Id>1</Id>
      <NAME>Smith</NAME>
    </ROW>
  </ROWSET>

Note the element name for the Id column matches the case with the column name.

XML Element Name to Column Name Mapping

Oracle9i and higher handles the mapping of XML element names to column names differently from Oracle 8.1.7 when using XML for data manipulation in the database. This section demonstrate these differences with changes to the following specialchars table involving the some id column.

// Create the specialchars table
create table specialchars ("some id" number, name varchar2(255));

Note that the specialchars table has a some id column that contains a space character. The space character is not allowed in an XML element name.

// Create the view with the escaped column name 
create view view1(some_x0020_id, name) as select * from specialchars;
 
// Create the view with the column name with no invalid character
create view view2(someid, name) as select * from specialchars;

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <SOME_X0020_id>1</SOME_X0020_id >
      <NAME>&lt;Jones&gt;</NAME>
    </ROW>
  </ROWSET>

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <SOMEID>2</SOMEID>
      <NAME>&lt;Jones&gt;</NAME>
      </ROW>
  </ROWSET>

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <some_x0020_id>2</some_x0020_id>
      <NAME>&lt;Jones&gt;</NAME>
    </ROW>
  </ROWSET>

CmdObj.XmlCommandType = OracleXmlCommandType.Insert;
CmdObj.XmlSaveProperties.Table = "specialchars";
string[] ucols = new string[2];
ucols[0] = "some_x0020_id";
ucols[1] = "NAME";
CmdObj.XmlSaveProperties.UpdateColumnsList = ucols;
CmdObj.ExecuteNonQuery();

Object-Relational Data

Changes in an XML document can also be saved to object-relational data. Each item in a collection can be specified in one of the following ways in the XML document:

• By enclosing the database type name of the item as the XML element name.

• By enclosing the name of the database column holding the collection with _ITEM appended as the XML element name.

Multiple Tables

Oracle Database does not support saving changes to multiple relational tables that have been joined together. In this case, Oracle recommends that you create a view on those relational tables, and then update that view. If the view is not updatable, triggers can be used instead.

Commits

When the changes in an XML document are made, either all the changes are committed, or if an error occurs, any changes that were made are rolled back.