16 Using Oracle Data Provider for .NET with Oracle XML DB

ODP.NET XML Support and Oracle XML DB

ODP.NET supports XML natively in the database, through Oracle XML DB. ODP.NET XML support includes the following features:

• Stores XML data natively in Oracle Database as XMLType.

• Accesses relational and object-relational data as XML data from Oracle Database to a Microsoft .NET environment, and processes the XML using Microsoft .NET framework.

• Saves 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. Provides the following XML-specific classes:

  • OracleXmlType

  • OracleXmlStream

  • OracleXmlQueryProperties

  • OracleXmlSaveProperties

ODP.NET Sample Code

Example 16-1 retrieves XMLType data from the database to .NET and outputs the results:

//Create OracleCommand and query XMLType 
OracleCommand xmlCmd = new OracleCommand(); 
poCmd.CommandText = "SELECT po FROM po_tab";
poCmd.Connection = conn;
// Execute OracleCommand and output XML results to an OracleDataReader 
OracleDataReader poReader = poCmd.ExecuteReader(); 
// ODP.NET native XML data type object from Oracle XML DB 
OracleXmlType poXml; 
string str = ""; //read XML results 
while (poReader.Read()) 
{ 
  // Return OracleXmlType object of the specified XmlType column 
  poXml = poReader.GetOracleXmlType(0);     
  // Concatenate output for all the records 
  str = str + poXml.Value; 
} //Output XML results to the screen 
Console.WriteLine(str);