An XVIEW is a table representation of data stored in an XMLTYPE. These allow for easy and fast searching. An XVIEW is accessed via a database view in the related database schema, although its underlying table belongs to the XVIEWMGR schema.
Step 1: Write the SQL that will generate the table for the XVIEW. This will use the Oracle function EXTRACTVALUE() to retrieve data from the xml. All columns should be aliased including any ‘id’ columns, these should be aliased such as ‘employee_id’.
Once the query is written, a condition must be applied, this will be:
WHERE id = :key1
This will allow the XVIEW to work for each row. The column name ‘id’ should be replaced with the id column of your table.
Step 2: In order for the XVIEW to be created, the XVIEW needs to create a foreign key to your table. In order for it to do this, it will need to be granted access. Do this by running the SQL statement:
GRANT REFERENCES, SELECT ON schema.table_name TO xviewmgr;
Step 3: Connect to XVIEWMGR and open the table named ‘XVIEW_DEFINITIONS’. This is where the XVIEWS and defined and created. Add a new row and fill in the following columns as follows:
Column Name |
Description |
XVIEW_OWNER |
This should be the name of the schema that will own the XVIEW. |
XVIEW_NAME |
Choose a name to use for the XVIEW. This will usually be something similar to XVIEW_TABLENAME. |
PROCESS_WHEN |
This can take the following values: |
PROCESS_KEYS |
This can take the following values: |
PROCESS_HOW |
This can take the following values: |
INTERNAL_TABLE_NAME |
This will be the name of the table that is created for the XVIEW and will reside in XVIEWMGR. This name is usually quite short and is often abbreviated. |
DATA_TABLESPACE |
This is the name of the tablespace to be used for the table. |
BASETABLE_OWNER |
This is the name of the schema that owns the table that the XVIEW is being created from. |
BASETABLE_NAME |
This is the name of the table that the XVIEW is being created from. |
BASETABLE_KEY_COLUMN_1 |
These two are the primary keys of the basetable. Most tables will only use BASETABLE_KEY_COLUMN_1, but in some examples where a compound foreign key is used, both fields must be filled in. Usually with ‘id’. |
BASETABLE_STATEMENT |
This will be the query you wrote in step 1, without a ; or / character to end the statement. |
XVIEW_KEY_COLUMN_1 |
Similar to the basetable_key_columns, this will be the name of the aliased columns in the XVIEW. Similar to ‘employee_id’ |
XVIEW_STATEMENT |
This is a simple query that looks like this: |
STEP 3: Now that the required columns have been filled in, the XVIEW can be created. Find the column in the table named ‘ENTER_COMMAND_HERE’ and enter a ‘C’ for ‘create’ (lower or upper case) here.
Now commit the newly entered data and click the refresh button.
Continue to click the refresh button until the LAST_STATUS column becomes ‘POPULATED’ and the LAST_SYSTEM_MESSAGE column starts with Success.
If the LAST_SYSTEM_MESSAGE column has a Failed message, your XVIEW was not created. You can read the error message by double clicking on it. Once you feel you have fixed whatever caused the error you can enter C again.
Step 4: Your XVIEW has now been created, if you go back to the basetable schema, and look at the views you will find a view for the XVIEW you just made. Open it and you will see that all the data from inside you XML is now in a table format. This table will automatically update itself as the basetable changes.
This view can be selected from just like a table.
The ‘ENTER_COMMAND_HERE’ column does not have to be ‘C’. It could also take the following values:
D for Drop
F for Fix
Step 5: Assuming that the XVIEW has been created successfully, now the column definitions can be corrected. Use the schema browser to navigate to xviewmgr.xview_definition_cols and filter by the name of your newly create XVIEW.
In this table, you can alter the definition for individual columns, set the data type, set its precision and scale (if numeric) or is char_length (if a string). You can also define indexes in this table for you XVIEW.
When the XIEW is created, it will try to guess the definition for each column and this can be helped by casting values to the correct datatype.
Once you are happy with all the column definitions, go back to xviewmgr.xview_definitions and enter an ‘F’ in the ‘ENTER_COMMAND_HERE’ column and commit to fix the column definitions and rebuild the XVIEW.
Step 1:
SELECT
id employee_id
, EXTRACTVALUE(xml_data, '/ROOT/EMPLOYEE/ID')
, EXTRACTVALUE(xml_data, '/ROOT/EMPLOYEE/TITLE')
, EXTRACTVALUE(xml_data, '/ROOT/EMPLOYEE/FORENAME')
, EXTRACTVALUE(xml_data, '/ROOT/EMPLOYEE/SURNAME')
, EXTRACTVALUE(xml_data, '/ROOT/EMPLOYEE/HIRE_DATE')
FROM scott.employee_search_xml
WHERE id = :key1
Step 2:
GRANT REFERENCES, SELECT ON scott.employee_search_xml TO xviewmgr;
Step 3:
XVIEW_OWNER |
SCOTT |
XVIEW_NAME |
XVIEW_EMPLOYEE_SEARCH_XML_NEW |
PROCESS_WHEN |
IN TRANSACTION |
PROCESS_KEYS |
IN MEMORY |
PROCESS_HOW |
MERGE DIFFS |
INTERNAL_TABLE_NAME |
EMP_SEARCH |
DATA_TABLESPACE |
TBSDATA |
BASETABLE_OWNER |
SCOTT |
BASETABLE_NAME |
EMPLOYEE_SEARCH_XML |
BASETABLE_KEY_COLUMN_1 |
ID |
BASETABLE_KEY_COLUMN_2 |
|
BASETABLE_STATEMENT |
SELECT |
XVIEW_KEY_COLUMN_1 |
EMPLOYEE_ID |
XVIEW_KEY_COLUMN_2 |
|
XVIEW_STATEMENT |
SELECT * FROM :viewtable WHERE employee_id = :key1 |
Step 4: Enter either an upper of lower case ‘C’ in the ‘ENTER_COMMAND_HERE’ column. Commit this and click refresh until the XVIEW is created, or fails.
The LAST_STATUS and LAST_SYSTEM_MESSAGES show that the XVIEW has been successfully created.
By going to the SCOTT schema and looking at the views, the new XVIEW called XVIEW_EMPLOYEE_SEARCH_XML_NEW can bee see and can be selected from with a statement like this:
SELECT * FROM scott.xview_employee_search_xml_new
Step 5: Alter the definitions for each of the columns in your XVIEW if required and enter an ‘F’ in the column ‘ENTER_COMMAND_HERE’ in the xviewmgr.xview_definitions table and commit to fix the XVIEW.
N.B. If you now go back to XVIEWMGR and look at the synonyms and triggers, you will see that several triggers and a synonym for your XVIEW have been created automatically. The trigger name will contain the INTERNAL_TABLE_NAME whilst the synonyms will use the XVIEW_NAME.
If the XVIEW is being created for use in a FOX module that is not for training purposes, the XVIEW will need to be check into PVCS. When connected to XVIEWMGR, find a view named ‘XVIEW_DEFINITION_METADATA’. In this, find the record with the XVIEW_NAME of the XVIEW you just created. Save the XVIEW_METADATA xml as FILE_NAME. This can file can then be checked into PVCS under \CodeSource\XviewDefinitions.
EXERCISES
If you have not already done so, create a copy of EMPLOYEE_SEARCH_XML (found in the SCOTT schema). Name is XX_EMPLOYEE_SEARCH_XML where XX are your initials.
Create an XVIEW for XX_EMPLOYEE_SEARCH_XML.