Skip Headers

Oracle® Data Provider for .NET Developer's Guide
10g Release 1 (10.1)

Part Number B10117-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

ODP.NET XML Support

From Oracle8i release 3 (8.1.7) and on, Oracle Database allows the extraction of data from relational and object-relational tables and views as XML documents. The use of XML documents for insert, update, and delete operations to the database server is also allowed.

With Oracle9i release 2 (9.2), Oracle Database supports XML natively in the database, through Oracle XML Database (Oracle XML DB), a distinct group of technologies related to high-performance XML storage and retrieval. Oracle XML DB is an evolution of the database that encompasses both SQL and XML data models in a highly interoperable manner, providing native XML support.


Note:

For database releases 8.1.7 and 9.0.1 only, certain OracleCommand methods require Oracle XML Developer's Kit (Oracle XDK) release 9.2 (Oracle XDK) or higher, to be installed in the database. The XDK can be downloaded from Oracle Technology Network (OTN).

For samples related to ODP.NET XML support, see the following directory:

ORACLE_BASE\ORACLE_HOME\ODP.NET\Samples

This section includes these topics:

Supported XML Features

XML support in ODP.NET provides the following features:

  • Store XML data natively in the database server as the Oracle database native type, XMLType.

  • Access relational and object-relational data as XML data from an Oracle Database instance into Microsoft .NET environment, process the XML using Microsoft .NET framework.

  • Save changes to the database server using XML data.

For the .NET application developer, these features include the following:

  • Enhancements to the OracleCommand, OracleConnection, and OracleDataReader classes

  • The following XML-specific classes:

OracleXmlType and Connection Dependency

The read-only Connection property of the OracleXmlType object holds a reference to the OracleConnection object used to instantiate the OracleXmlType object.

How the OracleXmlType object obtains a reference to an OracleConnection object depends on how the OracleXmlType object is instantiated:

  • Instantiated from an OracleDataReader using the GetOracleXmlType, GetOracleValue, or GetOracleValues method:

    The Connection property is set with a reference to the same OracleConnection object used by the OracleDataReader.

  • Instantiated by invoking an OracleXmlType constructor with one of the parameters of type OracleConnection:

    The Connection property is set with a reference to the same OracleConnection object provided in the constructor.

  • Instantiated by invoking an OracleXmlType(OracleClob) constructor:

    The Connection property is set with a reference to the OracleConnection object used by the OracleClob object.

An OracleXmlType object that is associated with one connection cannot be used with a different connection. For example, if an OracleXmlType object is obtained using OracleConnection A, that OracleXmlType object cannot be used as an input parameter of a command that uses OracleConnection B. By checking the Connection property of the OracleXmlType objects, the application can ensure that OracleXmlType objects are used only within the context of the OracleConnection referenced by its connection property. Otherwise, ODP.NET raises an exception.

Updating XMLType Data in the Database Server

Updating XMLType columns does not require a transaction. However, encapsulating the entire database update process within a transaction is highly recommended. This allows the updates to be rolled back if there are any errors.

XMLType columns in the database can be updated using the Oracle Data Provider for .NET in several ways:

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.

Input Binding

To update an XMLType column in the database, a SQL statement can be executed using static values. In addition, input parameters can be bound to SQL statements, anonymous PL/SQL blocks, or stored procedures to update XMLType columns. The parameter value can be set as .NET Framework Types, ODP.NET Types, or OracleXmlType objects.

While XMLType columns can be updated using the OracleXmlType object, having an instance of an OracleXmlType object does not guarantee that the XMLType column in the database can be updated.

Setting XMLType Column to NULL Value

Applications can set an XMLType column in the database to a NULL value, with or without input binding, as follows:

  • Setting NULL values in an XMLType column with Input Binding

    To set the XMLType column to NULL, the application can bind an input parameter whose value is DBNull.Value. This indicates to the OracleCommand that a NULL value is to be inserted.

    Passing in a null OracleXmlType object as an input parameter does not insert a NULL into the XMLType column. In this case, the OracleCommand raises an exception.

  • Setting NULL Values in an XMLType Column without Input Binding

    The following example demonstrates setting NULL values in an XMLType column without input binding:

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

    An application can set a NULL value in the XMLType column by explicitly inserting a NULL or by not inserting anything into that column as in the following examples:

    insert into xml_table(xmltype_col) values(NULL);
    update xml_table t set t.xmltype_col=NULL;
    
    
Setting XMLType Column to Empty XML Data

The XMLType column can be initialized with empty XML data, using a SQL statement:

// 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/>'));

Updating XML Data in OracleXmlType

There are several ways that XML data can be updated in an OracleXmlType object.

  • The XML data can be updated by passing an XPATH expression and the new value to the Update method on the OracleXmlType object.

  • The XML data can be retrieved on the client side as the .NET Framework XmlDocument object using the GetXmlDocument method on the OracleXmlType object. This XML data can then be manipulated using suitable .NET Framework classes. A new OracleXmlType can be created with the updated XML data from the .NET Framework classes. This new OracleXmlType is bound as an input parameter to an update or insert statement.

Special Characters in XML

The following characters have special meaning in XML. For more information, refer to the XML 1.0 specifications

Table 3-18 Special Characters

Special Character Meaning in XML Entity Encoding
<
Begins an XML tag &lt;
>
Ends an XML tag &gt;
" Quotation mark &quot;
'
Apostrophe or single quotation mark &apos;
&
Ampersand &amp;

When these characters appear as data in an XML element, they are replaced with their equivalent entity encoding.

Also certain characters are not valid in XML element names. When SQL identifiers (such as column names) are mapped to XML element names, these characters are converted to a sequence of hexadecimal digits, derived from the Unicode encoding of the character, bracketed by an introductory underscore, a lowercase x and a trailing underscore. For example, the space is not a valid character in an XML element name. If a SQL identifier contains a space character, then in the corresponding XML element name, the space character is replaced by _x0020_, which is based on Unicode encoding of the space character.

Retrieving Query Result Set as XML

This section discusses retrieving the result set from a SQL query as XML data.

Handling Date and Time Format

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

Table 3-19 Database Release Date and Time Differences When Retrieving Data

Database Release Date and Time Format Supported
Oracle8i release 3 (8.1.7) and Oracle9i release 1 (9.0.x) Oracle DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE type data is always retrieved in the result XML document as the ISO Date and Time Format: YYYY-MM-DDThh:mm:ss.sss (ISO Format notation).

The following string is the ISO Date and Time Format notation represented in the Oracle Date and Time Format notation: YYYY-MM-DD"T"HH:MI:SS.FF3.

Oracle9i release 2 (9.2.x) Oracle DATE type data is retrieved in the format specified using the NLS_DATE_FORMAT in the session.

TIMESTAMP and TIMESTAMP WITH TIME ZONE type data is retrieved in the format specified using the NLS_TIMESTAMP_FORMAT and the NLS_TIMESTAMP_TZ_FORMAT in the session.

If the result XML document is used to save changes to database releases 8.1.7, 9.0.x, or 9.2.x, then all DATE and TIMESTAMP data must be retrieved in the XML document as the following ISO Date and Time Format: YYYY-MM-DDThh:mm:ss.sss (ISO Format notation).

To do this, before the query is executed, the application must explicitly perform an ALTER SESSION command on the session for the following NLS session parameters:

  • NLS_DATE_FORMAT - Must be set to the following Oracle Date and Time Format: YYYY-MM-DD"T"HH:MI:SS

  • NLS_TIMESTAMP_FORMAT - Must be set to the following Oracle Date and Time Format: YYYY-MM-DD"T"HH:MI:SS.FF3

  • NLS_TIMESTAMP_TZ_FORMAT - Must be set to the following Oracle Date and Time Format: YYYY-MM-DD"T"HH:MI:SS.FF3

Oracle Database 10g
Oracle DATE type data is retrieved in the format specified using the NLS_DATE_FORMAT in the session.

TIMESTAMP and TIMESTAMP WITH TIME ZONE data is retrieved in the format specified using the NLS_TIMESTAMP_FORMAT and the NLS_TIMESTAMP_TZ_FORMAT in the session.

For interoperability with Oracle9i release 1 (9.0.x), Oracle9i release 2 (9.2.x), and Oracle 8.1.7, Oracle recommends that all DATE and TIMESTAMP data be retrieved in the XML document as the following ISO Date and Time Format: YYYY-MM-DDThh:mm:ss.sss (ISO Format notation).


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.

Retrieving Results from Oracle 8.1.7

When retrieving the query results as XML from an Oracle 8.1.7 database, the SQL identifiers in the query select-list cannot contain characters that are not valid in XML element names. To handle the lack of support for this feature in Oracle 8.1.7, the SQL query in the following example can be used to get a result as a XML document from the specialchars table:

select "some id" as "some_x0020_id", name from specialchars;
Retrieving Results from Oracle9i or Higher

When retrieving the query results as XML from Oracle9i and higher, the SQL identifiers in the query select-list can contain characters that are not valid in XML element names. When these SQL identifiers (such as column names) are mapped to XML element names, each of these characters are converted to a sequence of hexadecimal digits, derived from the Unicode encoding of the characters, bracketed by an introductory underscore, a lower case x, and a trailing underscore.

Thus, with an Oracle9i database, the SQL query in the following example can be used to get a result as an XML document from the specialchars table:

select "some id", name from specialchars;

Improving Default Mapping

If this default mapping of SQL identifiers to XML element names is not adequate, you can improve the mapping by the following techniques:

  • Modify the source. Create an object-relational view over the source schema, and make that view the new source.

  • Use cursor subqueries and cast-multiset constructs in the SQL query.

  • Create an alias for the column or attribute names in the SQL query. Prepend the aliases with an at sign (@) to map them to XML attributes instead of XML elements.

  • Modify the XML Document. Use XSLT to transform the XML document. Specify the XSL document and parameters. The transformation is done automatically after the XML document is generated from the relational data. Note that this is not the best solution in terms of performance.

  • Specify the name of the root tag and row tag used in the XML document.

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.

Data Manipulation Using XML

This section discusses making changes to the database using XML.

Handling of Date and Time Format

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

Table 3-20 Database Release Date and Time Differences When Saving Data

Database Release Date and Time Format Supported
Oracle8i release (8.1.7), Oracle9i release 1 (9.0.x), and Oracle9i release 2 (9.2.x) All DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE type data must be specified in the XML document in the ISO Date and Time Format YYYY-MM-DDThh:mm:ss.sss (ISO Format notation).
Oracle Database 10g All DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE type data must be specified in the XML document in the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT of the session, respectively.

If the DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE type data in the XML document is different from the session settings, then before saving the changes using the XML document, the application must explicitly perform an ALTER SESSION command on the session for the following NLS session parameters to reflect the format in the XML document:

  • NLS_DATE_FORMAT

  • NLS_TIMESTAMP_FORMAT

  • NLS_TIMESTAMP_TZ_FORMAT


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.

Saving changes to Oracle 8.1.7

In this scenario, with an Oracle 8.1.7 database, in order to save changes to the specialchars table using an XML document, a view must be created over the table and the changes saved to the view using XML data.

The column names in the view corresponding to the some id column in the table can be either a column name with no invalid characters or the escaped column name as in the following example.

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

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

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

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

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <SOMEID>2</SOMEID>
      <NAME>&lt;Jones&gt;</NAME>
      </ROW>
  </ROWSET>
Saving Changes to Oracle9i or higher

When an XML document is used to save changes to a table or view, the OracleCommand.XmlSaveProperties. UpdateColumnsList is used to specify the list of columns to update or insert.

With Oracle9i or higher, when an XML document is used to save changes to a column in a table or view and the corresponding column name contains any of the characters which are not valid in an XML element name, the escaped column name needs to be specified in the UpdateColumnsList property as in the following example.

The following XML document can be used to insert values (2, <Jones>) into the specialchars table.

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

The following code example specifies the list of columns to update or insert.

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();

Improving Default Mapping

If the default mapping is not adequate, you can improve the mapping by the following techniques:

  • Modify the target. Create an object-relational view over the target schema, and make the view the new target.

  • Modify the XML Document. Use XSLT to transform the XML document. Specify the XSL document and parameters. The transformation is done before the changes are saved. Note that this is not the best solution in terms of performance.

  • Specify the name of the row tag used in the XML document.

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.


See Also:

Oracle Database SQL Reference for the description and syntax of the CREATE VIEW command

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.