ODP.NET XML Support

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 the OracleDataAdapter.Update method is invoked, the OracleDataAdapter object ensures that the XMLType data is handled properly. The OracleDataAdapter object uses any custom SQL INSERT, UPDATE, or DELETE statements that are provided. Otherwise, valid SQL statements are generated by the OracleCommandBuilder object as needed to flush the changes to the database.

Updating with OracleCommand and OracleParameter

The OracleCommand class provides a powerful way of updating XMLType data, especially with the use of an OracleParameter object. 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 XML_TABLE(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-15 lists the date and time format handling when retrieving data, for different database releases.

Characters with Special Meaning in Column Data

If the data in any of the select list columns in the query contains any characters with special meaning in XML (see Table 3-14), 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 characters in the column data:

/* Database Setup
connect scott/tiger@oracle
drop table specialchars;
create table specialchars ("id" number, name varchar2(255));
insert into specialchars values (1, '<Jones>');
commit;
*/
 
// C#
 
using System;
using System.Data;
using System.Xml;
using Oracle.DataAccess.Client; 
 
class QueryResultAsXMLSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
    con.Open();
 
    // Create the command
    OracleCommand cmd = new OracleCommand("", con);
 
    // 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);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
  }
}

The following XML document is generated for that table: The XML entity encoding that represents the angle brackets appears in bold.

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

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 of 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, as shown in the following example:

// 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.

Oracle9i and later handles the mapping of SQL identifiers to XML element names differently from Oracle 8.1.7, when retrieving query results as XML from the database. This section demonstrates 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 column named some id that contains a blank space character. The space character is not allowed in an XML element name.

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, as shown in the following example:

// 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, which appears in bold in the example.

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 Date and Time Format

Table 3-16 lists the date and time format handling when saving data, 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 are insert, update, or delete changes.

The insert change must be in an XML document containing only rows to be inserted, the update changes only with rows to be updated, and the delete changes 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>

Characters with Special Meaning 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-14), these characters must be replaced with appropriate entity encoding, or be preceded by an escape character in the XML document, so that the data is stored correctly in the database table column. Otherwise, ODP.NET throws an exception.

The following example demonstrates how ODP.NET handles the angle bracket special characters in the column data, using entity encoding:

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

The following XML document can be used to insert values (1, '<Jones>') into the specialchars table. The XML entity encoding that represents the angle brackets appears in bold.

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

Characters with Special Meaning 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 that represents 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 that the element name for the Id column matches the case of the column name.

XML Element Name to Column Name Mapping

Oracle9i and later 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 demonstrates 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 column named some id that contains a blank 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>1</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>

/* Database Setup
connect scott/tiger@oracle
drop table specialchars;
create table specialchars ("some id" number, name varchar2(255));
insert into specialchars values (1, '<Jones>');
commit;
*/
 
// C#
 
using System;
using System.Data;
using System.Xml;
using Oracle.DataAccess.Client; 
 
class InsertUsingXmlDocSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected Successfully");
 
    // Create the command
    OracleCommand cmd = new OracleCommand("", con);
 
    // Set the XML command type to query.
    cmd.XmlCommandType = OracleXmlCommandType.Insert;
 
    // Set the XML document
    cmd.CommandText = "<?xml version = '1.0'?>\n" + "<ROWSET>\n" + "<ROW>\n" +
      "<some_x0020_id>2</some_x0020_id>\n" + "<NAME>&lt;Jones&gt;</NAME>\n" + 
      "</ROW>\n" + "</ROWSET>\n";
    cmd.XmlSaveProperties.Table = "specialchars";
 
    string[] ucols = new string[2];
 
    ucols[0] = "some_x0020_id";
    ucols[1] = "NAME";
    cmd.XmlSaveProperties.UpdateColumnsList = ucols;
 
    // Insert rows
    int rows = cmd.ExecuteNonQuery();
 
    Console.WriteLine("Number of rows inserted successfully : {0} ", rows);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
  }
}

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 save changes to multiple relational tables that have been joined together. Oracle recommends that you create a view on those relational tables, and then update that view. If the view cannot be updated, triggers can be used instead.

Commit Transactions

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