Inserting XML Data to Oracle Database

Now that you have defined the XMLType storage for employee XML documents, you might want to load some data into the employees XML schema-based XMLType table generated during the schema registration. The simplest way to do this is to use the INSERT SQL statement, as follows:

In the above example, you use the createSchemaBasedXML method of XMLType to explicitly identify the employee XML schema when inserting a new row into the employees table.

Now, if you try to issue the INSERT statement shown in the listing again, you will receive the following error message:

ERROR at line 1: ORA-00001: unique constraint (XMLUSR.EMP_PKEY) violated

As you can see, an attempt to insert the same row into the employees table fails due to a EMP_PKEY primary key constraint violation. If you recall, you define the EMP_PKEY primary key on the id attribute of the EMPLOYEE element in the XML schema registered as discussed in this section earlier. This constraint makes it impossible to insert two employee XML documents with the same ID into the employees table.

NOTE Another way to load data into the employees table is via one of the internet protocols supported by Oracle XML DB. This mechanism is discussed in the Taking Advantage of Standard Internet Protocols section later in this chapter.

Finally, it's worth noting that you can always delete a registered XML schema along with all the database objects generated during its registration. For example, to delete the employee XML schema registered as discussed earlier in this section, you might issue the following PL/SQL block:

NOTE Since the employees table is used in the subsequent examples, make sure to register the employee XML schema again as discussed earlier in this section. Also make sure to insert a row into the table as shown earlier in this section.

Besides deleting the employee XML schema, the above code deletes the employee_t object type and employees XMLType table generated during the schema registration process.

NOTE: For more information on using XML schemas in Oracle XML DB, you can refer to Oracle documentation: chapters: XML Schema Storage and Query: Basic and XML Schema Storage and Query: Advanced in the Oracle XML DB Developer's Guide. Also, for information on XML Schema language, you can refer to the W3C XML Schema Recommendation at http://www.w3.org/TR/xmlschema-0/" target="_blank" rel="nofollow".