Using XML with XML DB and ODP.NET
by Mark A. Williams

Learn how to use XML with an Oracle database, Oracle XML DB, and the Oracle Data Provider for .NET.

Downloads for this article: · Sample Code · Oracle Data Provider for .NET (ODP.NET)

Many developers already realize, and many more are beginning to realize, the powerful duo formed by the Oracle Database in conjunction with .NET applications. One of the many powerful features of the Oracle Database is its ability to work with XML. Using the Oracle Data Provider for .NET (ODP.NET) and Oracle XML DB, working with XML data in an Oracle database from a Visual Studio.NET application is as easy as working with traditional datatypes. (For an introduction to general .NET development with Oracle, see John Paul Cook's article "Build a .NET Application on the Oracle Database." See the "XML Support with ODP.NET" page for other samples of using XML and the Oracle Data Provider for .NET.)

This article explores the basics of working with XML data in an Oracle database using ODP.NET and Visual Studio. NET, paying particular attention to

• Loading an XML Schema into the database
• Loading an XML Instance Document into the database
• Creating a .NET application that performs DML operations on the XML data.

In order to successfully implement the code in this article, you will need access to an Oracle9i Release 2 or later database with XML DB installed, Visual Studio.NET, the Oracle 10g client software, and ODP.NET 10g. (The Oracle Database 10g download includes ODP.NET.)

In this article, I create a database user called "OTNXML" to illustrate working with XML data. If you do not have sufficient rights to create a user with the appropriate privileges in your database, you will need to coordinate with your database administrator to accomplish this task. I have broken the article down into a series of easy-to-follow steps, beginning with the creation of the database user.

NOTE: Creating a new user is not strictly necessary—if you already have a user with the same (or greater) privileges as those listed in Step 1, you can use that user for this article. Of course, you could also grant the missing privileges to an existing user as well. For example, the OE user, which is part of the Oracle sample schemas, has all of the required privileges except for the create any directory and the drop any directory privileges.

Step 1: Creating the Database User

In order to create the database user for this article, log on to the database as an administrative user (a user with the DBA role) using SQL*Plus and execute the following statement:

-- create the database user
-- adjust tablespace names as necessary for your environment
create user otnxml identified by demo
default tablespace users
temporary tablespace temp
quota unlimited on users;

Once the user has been created, appropriate privileges and roles should be granted. Rather than grant the "traditional" connect and resource roles, I have elected to grant only the system privileges needed.

-- grant system privileges and roles to the user
grant create session,
      alter session,
      create table,
      create trigger,
      create type,
      create any directory,
      drop any directory,
      xdbadmin
to    otnxml;

While most of these privileges should be familiar to you, the following synopsis serves to reinforce their purpose and to indicate their usage within this article:

• create session—This privilege allows the user to create a session with the database. Without this privilege the user would not be able to connect (or log on) to the database.
• alter session—This privilege allows the user to alter attributes related to the session with the database. This is required to successfully execute the registerSchema procedure.
• create table—This privilege allows the user to create a table in the database.
• create trigger—This privilege allows the user to create a trigger on a table owned by the user.
• create type—This privilege allows the user to create a new type in the database. The XML elements will be persisted in the database using types.
• create any directory—This privilege allows the user to create a directory object. A directory object will be used to load the XML Instance Document into the database. This is a fairly powerful privilege and should be granted only to trusted users. Because directory objects reside in the system namespace within the database, the "any" keyword is required. There is no user-level "create directory" privilege.
• drop any directory—This privilege is the counterpart to the create any directory privilege. It allows the user to drop a directory object. Like the create any directory privilege, this privilege should be granted only to trusted users due to its system-level nature.
• xdbadmin role —This privilege allows the user to create directories in the XML DB engine

NOTE: Due to the elevated rights afforded by the create any directory and the drop any directory privileges, you may wish to revoke these after working through this article if you chose not to drop the user. You may also wish to remove the directory object that you will create.

Step 2: Creating the XML Schema

Since the XML Schema document is a text-based document, you can create it using any text editor of your choice. There are various tools to graphically create and manipulate the schema document as well. However, in order to keep this article focused on using XML within the Oracle database from .NET, rather than on XML editing and management tools, I used Notepad to create the following simple schema file, which I have named "Player.xsd". The schema and the associated data represent a simple "player" entity, such as a player on a football (a.k.a. soccer) team.

<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xdb="http://xmlns.oracle.com/xdb"
            xdb:storeVarrayAsTable="true"
            version="1.0">
  <xsd:element name="Player" xdb:defaultTable="PLAYER">
    <xsd:complexType xdb:SQLType="PLAYER_T">
      <xsd:sequence>
        <xsd:element name="PlayerName" type="xsd:string" minOccurs="1"
             xdb:SQLName="PLAYER_NAME" />
        <xsd:element name="PlayerPosition" type="xsd:string" minOccurs="1"
             xdb:SQLName="PLAYER_POSITION" />
        <xsd:element name="PlayerNumber" type="xsd:positiveInteger"
             minOccurs="1" xdb:SQLName="PLAYER_NUMBER" />
        <xsd:element name="PlayerClub" type="xsd:string" minOccurs="1"
             xdb:SQLName="PLAYER_CLUB" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The XML Schema document is a basic schema document with perhaps one exception: It contains Oracle XML DB annotations. Annotations are a part of the World Wide Web Consortium (W3C) XML Schema Recommendation, and they serve the purpose of permitting vendor-specific information to be added to the schema definition. The annotations also allow the default mapping between the XML types and the SQL types to be overridden. This is the primary purpose of the annotations in this schema document. In order to use the XML DB annotations, the namespace must be identified in the root of the document. This is accomplished by the following text taken from the schema document:

xmlns:xdb="http://xmlns.oracle.com/xdb"

As you can see, I have elected to use the text "xdb" as the shortened identifier for this namespace throughout the schema document. In order to store a collection as a nested table, the following text is included in the document root:

xdb:storeVarrayAsTable="true"

In order to specify the name of the table inside of the database that should be used to hold the data, I have included the following text in the root element definition:

xdb:defaultTable="PLAYER"

NOTE: The default table is created by Oracle and must not be the name of an existing table. For example, you can not "pre-create" this table and tell Oracle to load the data into it.

This annotation indicates to the XML DB engine in the database that the table name used to store the data represented by this schema should be called "PLAYER". The remaining annotations are straightforward in name and function. The "SQLName" annotation indicates the column name in a table, and the "SQLType" annotation indicates either the column datatype or the database type object, as appropriate. For example, the following text indicates that the XML complex type should be stored in the database in a database type called "PLAYER_T":

xdb:SQLType="PLAYER_T"

Step 3: Registering the XML Schema Document

In order to register the XML Schema document with XML DB in the database, save the "Player.xsd" file in a folder where you have read/write access. There are several ways to load a file into the database using XML DB. I will illustrate the process using a directory object and the DBMS_XMLSCHEMA PL/SQL package.

NOTE: The directory object must represent an operating system directory that is accessible to the database.

1. Connect to the database as the OTNXML user, and create a directory object that will represent the operating system directory where the XML Schema and Instance Documents reside.

   
   -- create the directory object that represents the directory
   -- where the schema and instance documents reside
   create directory OTNXML_DIR as 'C:\My Projects\OTN\XML Schema\XML';
   

2. Register the XML Schema with XML DB by using the DBMS_XMLSCHEMA package.

   
   -- register the xml schema with xml db
   begin
   dbms_xmlschema.registerSchema(
     schemaurl=>'http://localhost:8080/home/OTNXML/source/xsd/Player.xsd',
     schemadoc=>bfilename('OTNXML_DIR','Player.xsd'),
     local=>TRUE,
     gentypes=>TRUE,
     genbean=>FALSE,
     force=>FALSE,
     owner=>'OTNXML',
     csid=>nls_charset_id('AL32UTF8')
   );
   end;
   /
   

Now that the schema has been successfully registered in the database, you can view the objects that were created on your behalf during the registration process. These object names and types are a result of the annotations included in the schema definition file as well as system-generated names and types. For example, in Step 2 you indicated that the default type for the complex type should be stored in an Oracle type called "PLAYER_T". Here you can see that Oracle has created that type on your behalf.

select   object_name,
         object_type,
         status
from     user_objects
order by object_name,
         object_type;

Results of the statement:

OBJECT_NAME                      OBJECT_TYPE         STATUS
-------------------------------- ------------------- -------
EXTRADATA177_L                   LOB                 VALID
NAMESPACES178_L                  LOB                 VALID
PLAYER                           TABLE               VALID
PLAYER$xd                        TRIGGER             VALID
PLAYER_T                         TYPE                VALID
SYS_C004803                      INDEX               VALID
SYS_XDBPD$176_L                  LOB                 VALID

7 rows selected.

Step 4: Loading the XML Instance Document

In order to illustrate loading an XML Instance Document, I created a small file that conforms to the schema specified in the "Player.xsd" file. Like the "Player.xsd" file, this file is a simple text file that I created using Notepad. The contents of the "Player.xml" file are as follows:

<?xml version="1.0"?>
<Player
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation=
      "http://localhost:8080/home/OTNXML/source/xsd/Player.xsd">
  <PlayerName>Steven Gerrard</PlayerName>
  <PlayerPosition>Midfield</PlayerPosition>
  <PlayerNumber>8</PlayerNumber>
  <PlayerClub>Localhost</PlayerClub>
</Player>

The file is identified as an XML Instance Document by including the following text in the document root:

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

In addition, you can see that the schema that was just registered is also referenced in the document root:

xsi:noNamespaceSchemaLocation=
  "http://localhost:8080/home/OTNXML/source/xsd/Player.xsd">

I chose to create the XML Instance Document in the same directory as the schema document ("C:\My Projects\OTN\XML Schema\XML" on my machine) in order to reuse the directory object. The Instance Document can be loaded with the following statement:

insert into player
values
(
  XMLType
  (
    bfilename('OTNXML_DIR','Player.xml'),
    nls_charset_id('AL32UTF8')
  )
);

NOTE: Be sure to issue a commit after the data has been loaded.

To quickly verify that the data was loaded, you can enter the following statement in SQL*Plus:

select object_value from player;

Results of the statement:

OBJECT_VALUE
-------------------------------------------------------------------------------
<?xml version="1.0"?>
<Player xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/home/OTNXML/source/xsd/Player.xsd">
  <PlayerName>Steven Gerrard</PlayerName>
  <PlayerPosition>Midfield</PlayerPosition>
  <PlayerNumber>8</PlayerNumber>
  <PlayerClub>Localhost</PlayerClub>
</Player>


1 row selected.

As you can see, this statement produces the XML representation of the data (in other words, the Instance Document).

Step 5: Performing DML Operations on the XML Data

You have completed all the setup work, and it is now time to launch Visual Studio.NET and create an application that manipulates the XML data in the database. After launching Visual Studio.NET, create a new Windows Forms project and add a reference to the Oracle Data Provider for .NET to the project. The application consists of a simple form that will be used to perform the following tasks:

• Connect to the database
• Retrieve a player
• Insert a player
• Update a player
• Delete a player

I will first present the code that accomplishes these tasks. After presenting the code, I will demonstrate the application in action. In order to perform these tasks, create a form resembling the following:

Of course the first step in performing these operations is to create a connection to the database. I have created a form-level field that represents the connection. The field is declared as:

// C#:
private OracleConnection conn;

'// VB.NET:
Private conn As OracleConnection

The field is initialized in the form load event as follows:

// C#:
private void MainForm_Load(object sender, System.EventArgs e)
{
  // create new instance of Oracle Connection class
  conn = new OracleConnection();
}

'// VB.NET:
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  '// create new instance of Oracle Connection class
  conn = New OracleConnection
End Sub

The code behind the Connect button creates the connection to the database. This code is fairly boilerplate in nature. It creates a connection to the database as the OTNXML user you created earlier. Upon a successful connection, it disables the Connect button, enables the remaining buttons (I created them as initially disabled), and finally displays a message indicating that a successful connection has been established. In the event of an exception occurring, the code displays a basic message box indicating what exception has occurred.

// C#:
private void btnConnect_Click(object sender, System.EventArgs e)
{
  Cursor.Current = Cursors.WaitCursor;

  // adjust the connection string as necessary for your environment
  string connstr = "User Id=OTNXML; Password=DEMO; Data Source=ORANET";

  // set the connection string value for the connection object
  conn.ConnectionString = connstr;

  // attempt to open the connection
  // if failure display error message
  // if success display simple success message
  try
  {
    conn.Open();
  }
  catch (Exception ex)
  {
    MessageBox.Show(this, ex.Message, Application.ProductName,
      MessageBoxButtons.OK, MessageBoxIcon.Error);
  }
  finally
  {
    if (conn.State == ConnectionState.Open)
    {
      // disable the connect button
      btnConnect.Enabled = false;

      // enable the remaining buttons
      btnGetPlayer.Enabled = true;
      btnInsertPlayer.Enabled = true;
      btnUpdatePlayer.Enabled = true;
      btnDeletePlayer.Enabled = true;

      MessageBox.Show(this, "Successfully connected to database.",
        Application.ProductName, MessageBoxButtons.OK,
        MessageBoxIcon.Information);
    }
  }

  Cursor.Current = Cursors.Default;
}

'// VB.NET:
Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
  Cursor.Current = Cursors.WaitCursor

  '// adjust the connection string as necessary for your environment
  Dim connstr As String = "User Id=OTNXML; Password=DEMO; Data Source=ORANET"

  '// set the connection string value for the connection object
  conn.ConnectionString = connstr

  '// attempt to open the connection
  '// if failure display error message
  '// if success display simple success message
  Try
    conn.Open()
  Catch ex As Exception
    MessageBox.Show(Me, ex.Message, Application.ProductName,
      MessageBoxButtons.OK, MessageBoxIcon.Error)
  Finally
    If (conn.State = ConnectionState.Open) Then
      '// disable the connect button
      btnConnect.Enabled = False

      '// enable the remaining buttons
      btnGetPlayer.Enabled = True
      btnInsertPlayer.Enabled = True
      btnUpdatePlayer.Enabled = True
      btnDeletePlayer.Enabled = True

      MessageBox.Show(Me, "Successfully connected to database.",
        Application.ProductName, MessageBoxButtons.OK,
        MessageBoxIcon.Information)
    End If
  End Try

  Cursor.Current = Cursors.Default
End Sub

After connecting to the database, you can retrieve a player such as the one that was loaded in the previous step. The code behind the Get Player button retrieves a player based on the player's name. The SQL statement to retrieve a player retrieves the player as relational data rather than retrieving the XML document itself. You will see an example of using the XML document in the code to insert a player.

// C#:
private void btnGetPlayer_Click(object sender, System.EventArgs e)
{
  Cursor.Current = Cursors.WaitCursor;

  // build the sql statement to retrieve the xml
  // data as relational data
  StringBuilder sbSQL = new StringBuilder();
  sbSQL.Append("select   extractvalue(object_value, '/Player/PlayerPosition'), ");
  sbSQL.Append("         extractvalue(object_value, '/Player/PlayerNumber'), ");
  sbSQL.Append("         extractvalue(object_value, '/Player/PlayerClub') ");
  sbSQL.Append("from     player ");
  sbSQL.Append("where    existsNode(object_value, :1) = 1");

  // create an input parameter for the player name
  OracleParameter p1 = new OracleParameter();
  p1.OracleDbType = OracleDbType.Varchar2;
  p1.Direction = ParameterDirection.Input;
  p1.Value = "/Player[PlayerName=\"" + txtPlayerName.Text + "\"]";

  OracleCommand cmd = new OracleCommand();
  cmd.CommandText = sbSQL.ToString();
  cmd.Connection = conn;
  cmd.Parameters.Add(p1);

  OracleDataReader dr = cmd.ExecuteReader();

  if (dr.Read())
  {
    // assign the values to the text boxes
    txtPlayerPosition.Text = dr[0].ToString();
    txtPlayerNumber.Text = dr[1].ToString();
    txtPlayerClub.Text = dr[2].ToString();

    dr.Close();
  }
  else
  {
    MessageBox.Show(this, "Player Not Found", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
  }

  dr.Dispose();
  cmd.Dispose();

  Cursor.Current = Cursors.Default;
}

'// VB.NET:
Private Sub btnGetPlayer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetPlayer.Click
  Cursor.Current = Cursors.WaitCursor

  '// build the sql statement to retrieve the xml
  '// data as relational data
  Dim sbSQL As StringBuilder = New StringBuilder
  sbSQL.Append("select   extractvalue(object_value, '/Player/PlayerPosition'), ")
  sbSQL.Append("         extractvalue(object_value, '/Player/PlayerNumber'), ")
  sbSQL.Append("         extractvalue(object_value, '/Player/PlayerClub') ")
  sbSQL.Append("from     player ")
  sbSQL.Append("where    existsNode(object_value, :1) = 1")

  '// create input parameter for the player name
  Dim p1 As OracleParameter = New OracleParameter
  p1.OracleDbType = OracleDbType.Varchar2
  p1.Direction = ParameterDirection.Input
  p1.Value = "/Player[PlayerName=""" + txtPlayerName.Text + """]"

  Dim cmd As OracleCommand = New OracleCommand
  cmd.CommandText = sbSQL.ToString()
  cmd.Connection = conn
  cmd.Parameters.Add(p1)

  Dim dr As OracleDataReader = cmd.ExecuteReader()

  If (dr.Read()) Then
    '// assign the values to the text boxes
    txtPlayerPosition.Text = dr(0).ToString()
    txtPlayerNumber.Text = dr(1).ToString()
    txtPlayerClub.Text = dr(2).ToString()
    dr.Close()
  Else
    MessageBox.Show(Me, "Player Not Found", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)
    dr.Dispose()
    cmd.Dispose()
    Cursor.Current = Cursors.Default
  End If
End Sub

The application exposes the capability to insert a player into the database, and the code behind the Ins Player button inserts a new XML Instance Document that conforms to the XML Schema you created and loaded into the XML DB repository. After creating the XML Instance Document, the code binds the variable holding the document into a typical insert statement, performs the insert operation, and displays a success or exception message.

// C#:
private void btnInsertPlayer_Click(object sender, System.EventArgs e)
{
  Cursor.Current = Cursors.WaitCursor;

  // create the xml document
  StringBuilder xmlDocument = new StringBuilder();
  xmlDocument.Append("<?xml version=\"1.0\"?>");
  xmlDocument.Append("<Player");
  xmlDocument.Append("xmlns:xsi=
    \"http://www.w3.org/2001/XMLSchema-instance\"");
  xmlDocument.Append("xsi:noNamespaceSchemaLocation=
    \"http://localhost:8080/home/OTNXML/source/xsd/Player.xsd\">");
  xmlDocument.Append(" <PlayerName>" + txtPlayerName.Text + "</PlayerName>");
  xmlDocument.Append("<PlayerPosition>" + txtPlayerPosition.Text +
    "</PlayerPosition>");
  xmlDocument.Append("<PlayerNumber>" + txtPlayerNumber.Text +
    "</PlayerNumber>");
  xmlDocument.Append("  <PlayerClub>" + txtPlayerClub.Text + "</PlayerClub>");
  xmlDocument.Append("</Player>");

  // create command object and set properties
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = conn;
  cmd.CommandText = "insert into player values (:1)";

  // create an input parameter to hold the xml document
  OracleParameter p = new OracleParameter();
  p.Direction = ParameterDirection.Input;
  p.Value = xmlDocument.ToString();
  cmd.Parameters.Add(p);

  // execute the insert
  // display message if failure
  try
  {
    cmd.ExecuteNonQuery();

    MessageBox.Show(this, "Player Inserted.", Application.ProductName,
      MessageBoxButtons.OK, MessageBoxIcon.Information);
  }
  catch (Exception ex)
  {
    MessageBox.Show(this, ex.Message, Application.ProductName,
      MessageBoxButtons.OK, MessageBoxIcon.Error);
  }

  p.Dispose();
  cmd.Dispose();

  Cursor.Current = Cursors.Default;
}

'// VB.NET:
Private Sub btnInsertPlayer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsertPlayer.Click
  Cursor.Current = Cursors.WaitCursor

  '// create the xml document
  Dim xmlDocument As StringBuilder = New StringBuilder
  xmlDocument.Append("<?xml version=""1.0""?>")
  xmlDocument.Append("<Player")
  xmlDocument.Append("xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""")
  xmlDocument.Append("xsi:noNamespaceSchemaLocation=
    ""http://localhost:8080/home/OTNXML/source/xsd/Player.xsd"">")
  xmlDocument.Append("<PlayerName>" + txtPlayerName.Text + "</PlayerName>")
  xmlDocument.Append("<PlayerPosition>" + txtPlayerPosition.Text +
    "</PlayerPosition>")
  xmlDocument.Append("<PlayerNumber>" + txtPlayerNumber.Text +
    "</PlayerNumber>")
  xmlDocument.Append("<PlayerClub>" + txtPlayerClub.Text + "</PlayerClub>")
  xmlDocument.Append("</Player>")

  '// create command object and set properties
  Dim cmd As OracleCommand = New OracleCommand
  cmd.Connection = conn
  cmd.CommandText = "insert into player values (:1)"

  '// create an input parameter to hold the xml document
  Dim p As OracleParameter = New OracleParameter
  p.Direction = ParameterDirection.Input
  p.Value = xmlDocument.ToString()
  cmd.Parameters.Add(p)

  '// execute the insert
  '// display message if failure
  Try
    cmd.ExecuteNonQuery()
    MessageBox.Show(Me, "Player Inserted.", Application.ProductName,
      MessageBoxButtons.OK, MessageBoxIcon.Information)
  Catch ex As Exception
    MessageBox.Show(Me, ex.Message, Application.ProductName,
      MessageBoxButtons.OK, MessageBoxIcon.Error)
    p.Dispose()
    cmd.Dispose()
  End Try

  Cursor.Current = Cursors.Default
End Sub

Similar to the code that inserts a player, the Updt Player button code creates an XML Instance Document, binds it to a placeholder variable in a SQL statement, and executes the statement to update the player. The SQL statement uses the player's name in the where clause to determine which player to update. The update is really a replacement of an existing XML Instance Document with a new one containing the updated data. A simple success or exception message completes this code.

// C#:
private void btnUpdatePlayer_Click(object sender, System.EventArgs e)
{
  Cursor.Current = Cursors.WaitCursor;

  // create the xml document that contains the changes
  StringBuilder xmlDocument = new StringBuilder();
  xmlDocument.Append("<?xml version=\"1.0\"?>");
  xmlDocument.Append("<Player");
  xmlDocument.Append("    xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"");
  xmlDocument.Append("    xsi:noNamespaceSchemaLocation=\"http://localhost:8080/home/OTNXML/source/xsd/Player.xsd\">");
  xmlDocument.Append("  <PlayerName>" + txtPlayerName.Text + "</PlayerName>");
  xmlDocument.Append("  <PlayerPosition>" + txtPlayerPosition.Text + "</PlayerPosition>");
  xmlDocument.Append("  <PlayerNumber>" + txtPlayerNumber.Text + "</PlayerNumber>");
  xmlDocument.Append("  <PlayerClub>" + txtPlayerClub.Text + "</PlayerClub>");
  xmlDocument.Append("</Player>");

  StringBuilder sbSQL = new StringBuilder();
  sbSQL.Append("update   player ");
  sbSQL.Append("set      object_value = :1 ");
  sbSQL.Append("where    existsNode(object_value, :2) = 1");

  // create an input parameter to hold the xml document
  OracleParameter p1 = new OracleParameter();
  p1.OracleDbType = OracleDbType.Varchar2;
  p1.Direction = ParameterDirection.Input;
  p1.Value = xmlDocument.ToString();
  
  // create an input parameter for the player name
  OracleParameter p2 = new OracleParameter();
  p2.OracleDbType = OracleDbType.Varchar2;
  p2.Direction = ParameterDirection.Input;
  p2.Value = "/Player[PlayerName=\"" + txtPlayerName.Text + "\"]";

  // create command object and set properties
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = conn;
  cmd.CommandText = sbSQL.ToString();
  cmd.Parameters.Add(p1);
  cmd.Parameters.Add(p2);
  
  // execute the update
  // display message if failure
  try
  {
    cmd.ExecuteNonQuery();

    MessageBox.Show(this, "Player Updated.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
  }
  catch (Exception ex)
  {
    MessageBox.Show(this, ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
  }

  p2.Dispose();
  p1.Dispose();
  cmd.Dispose();

  Cursor.Current = Cursors.Default;
}

'// VB.NET:
Private Sub btnUpdatePlayer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdatePlayer.Click
  Cursor.Current = Cursors.WaitCursor

  '// create the xml document that contains the changes
  Dim xmlDocument As StringBuilder = New StringBuilder
  xmlDocument.Append("<?xml version=""1.0""?>")
  xmlDocument.Append("<Player")
  xmlDocument.Append("    xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""")
  xmlDocument.Append("    xsi:noNamespaceSchemaLocation=""http://localhost:8080/home/OTNXML/source/xsd/Player.xsd"">")
  xmlDocument.Append("  <PlayerName>" + txtPlayerName.Text + "</PlayerName>")
  xmlDocument.Append("  <PlayerPosition>" + txtPlayerPosition.Text + "</PlayerPosition>")
  xmlDocument.Append("  <PlayerNumber>" + txtPlayerNumber.Text + "</PlayerNumber>")
  xmlDocument.Append("  <PlayerClub>" + txtPlayerClub.Text + "</PlayerClub>")
  xmlDocument.Append("</Player>")

  Dim sbSQL As StringBuilder = New StringBuilder
  sbSQL.Append("update   player ")
  sbSQL.Append("set      object_value = :1 ")
  sbSQL.Append("where    existsNode(object_value, :2) = 1")

  '// create an input parameter to hold the xml document
  Dim p1 As OracleParameter = New OracleParameter
  p1.OracleDbType = OracleDbType.Varchar2
  p1.Direction = ParameterDirection.Input
  p1.Value = xmlDocument.ToString()

  '// create an input parameter for the player name
  Dim p2 As OracleParameter = New OracleParameter
  p2.OracleDbType = OracleDbType.Varchar2
  p2.Direction = ParameterDirection.Input
  p2.Value = "/Player[PlayerName=""" + txtPlayerName.Text + """]"

  '// create command object and set properties
  Dim cmd As OracleCommand = New OracleCommand
  cmd.Connection = conn
  cmd.CommandText = sbSQL.ToString()
  cmd.Parameters.Add(p1)
  cmd.Parameters.Add(p2)

  '// execute the update
  '// display message if failure
  Try
    cmd.ExecuteNonQuery()
    MessageBox.Show(Me, "Player Updated.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information)
  Catch ex As Exception
    MessageBox.Show(Me, ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

    p1.Dispose()
    p2.Dispose()
    cmd.Dispose()
  End Try

  Cursor.Current = Cursors.Default
End Sub

The final DML operation exposed by the application is the ability to delete a player. Like the retrieve and update code, this code uses the player's name as the basis for determining upon which player to perform the operation.

// C#:
private void btnDeletePlayer_Click(object sender, System.EventArgs e)
{
  Cursor.Current = Cursors.WaitCursor;

  // build the delete statement
  StringBuilder sbSQL = new StringBuilder();
  sbSQL.Append("delete ");
  sbSQL.Append("from     player ");
  sbSQL.Append("where    existsNode(object_value, :1) = 1");

  // create an input parameter for the player name
  OracleParameter p1 = new OracleParameter();
  p1.OracleDbType = OracleDbType.Varchar2;
  p1.Direction = ParameterDirection.Input;
  p1.Value = "/Player[PlayerName=\"" + txtPlayerName.Text + "\"]";

  // create command object and set properties
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = conn;
  cmd.CommandText = sbSQL.ToString();
  cmd.Parameters.Add(p1);

  // execute the delete
  try
  {
    cmd.ExecuteNonQuery();

    MessageBox.Show(this, "Player Deleted.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
  }
  catch (Exception ex)
  {
    MessageBox.Show(this, ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
  }

  cmd.Dispose();

  Cursor.Current = Cursors.WaitCursor;
}

'// VB.NET:
Private Sub btnDeletePlayer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeletePlayer.Click
  Cursor.Current = Cursors.WaitCursor

  '// build the delete statement
  Dim sbSQL As StringBuilder = New StringBuilder
  sbSQL.Append("delete ")
  sbSQL.Append("from     player ")
  sbSQL.Append("where    existsNode(object_value, :1) = 1")

  '// create an input parameter for the player name
  Dim p1 As OracleParameter = New OracleParameter
  p1.OracleDbType = OracleDbType.Varchar2
  p1.Direction = ParameterDirection.Input
  p1.Value = "/Player[PlayerName=""" + txtPlayerName.Text + """]"

  '// create command object and set properties
  Dim cmd As OracleCommand = New OracleCommand
  cmd.Connection = conn
  cmd.CommandText = sbSQL.ToString()
  cmd.Parameters.Add(p1)

  '// execute the delete
  Try
    cmd.ExecuteNonQuery()

    MessageBox.Show(Me, "Player Deleted.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information)

  Catch ex As Exception
 MessageBox.Show(Me, ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)

    cmd.Dispose()
  End Try

  Cursor.Current = Cursors.WaitCursor
End Sub

In order to clear the form, the code behind the Reset button simply sets the text box values to empty strings.

// C#:
private void btnReset_Click(object sender, System.EventArgs e)
{
  // "clear" the text box values
  txtPlayerName.Text = "";
  txtPlayerPosition.Text = "";
  txtPlayerNumber.Text = "";
  txtPlayerClub.Text = "";

  // assign focus to the player name text box
  txtPlayerName.Focus();
}

'// VB.NET:
Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
  '// "clear" the text box values
  txtPlayerName.Text = ""
  txtPlayerPosition.Text = ""
  txtPlayerNumber.Text = ""
  txtPlayerClub.Text = ""

  '// assign focus to the player name text box
  txtPlayerName.Focus()
End Sub

As you might expect, the code behind the Close button does nothing more than initiate a form close.

// C#:
private void btnClose_Click(object sender, System.EventArgs e)
{
  // simply close the form
  this.Close();
}

'// VB.NET:
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
  '// simply close the form
  Me.Close()
End Sub

The code in the form closing event checks if the database connection object is in an open state, and, if so, it closes and disposes of the object.

// C#:
private void MainForm_Closing(object sender,
  System.ComponentModel.CancelEventArgs e)
{
  if (conn.State == ConnectionState.Open)
  {
    // close connection and dispose connection object
    conn.Close();

    conn.Dispose();

    conn = null;
  }
}

'// VB.NET:
Private Sub MainForm_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
  If (conn.State = ConnectionState.Open) Then
    '// close connection and dispose connection object
    conn.Close()

    conn.Dispose()

    conn = Nothing
  End If
End Sub

Step 6: Running the Application

Now that you have created and loaded the XML Schema and XML Instance Documents and created the application, it is time to see it in action. The initial appearance of the form should resemble the following when the application is started:

After you click on the Connect button, the form should display a message box indicating that the connection was successfully established and the "DML buttons" should become enabled:

To view the player data for the XML Instance Document you loaded in Step 5, complete the Player Name text box with the name of the player (Steven Gerrard, if you used the same data I did), and click the Get Player button:

To create a new player, you can click the Reset button and enter new information in the text boxes or simply replace the existing text:

Once you are satisfied with the values for the various player attributes, click the Ins Player button. The new XML Instance Document containing the player information will be inserted into the database and a message box will be displayed:

To update a player, you can simply change the attribute (other than the player name). For example, I have changed my player number to 100:

Click the Updt Player button to update the data in the database:

Unfortunately, it appears that I did not make the cut for the Localhost Football Club and must delete myself. In order to delete a player, simply click the Del Player button:

To verify that the player has been deleted, click the Reset button and enter the player name in the Player Name text box:

Now, clicking on the Get Player button should result in a message box being displayed indicating that the requested player cannot be found:

When you have finished experimenting, click the Close button to terminate the application.

Summary

In this article, I introduced you to loading and registering an XML Schema Document in the XML DB repository and loading an XML Instance Document that conforms to that schema into the database. I then created a simple Windows form application that performs DML operations on the XML data. I concluded with a walkthrough of the application. This information should enable you to begin using XML data in the database from your Oracle-based .NET applications.