Oracle XML VIEWs
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Purpose The XQuery function
Dependencies
ALL_XML_VIEWS CDB_XML_VIEW_COLS USER_XML_VIEWS
ALL_XML_VIEW_COLS DBA_XML_VIEWS USER_XML_VIEW_COLS
CDB_XML_VIEWS DBA_XML_VIEW_COLS  
 
The demo at right uses the Oracle sample OE (Order Entry) schema is an enhanced version of that created by Oracle for their online docs conn oe/oe@pdbdev

SQL> desc purchaseorder
Name
-----------------------------------------------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relational TYPE "PURCHASEORDER_T"

SQL> desc purchaseorder_t
purchaseorder_t is NOT FINAL
Name                     Null?    Type
------------------------ -------- -------------------------
SYS_XDBPD$                        XDB.XDB$RAW_LIST_T
REFERENCE                         VARCHAR2(30 CHAR)
ACTIONS                           ACTIONS_T
REJECTION                         REJECTION_T
REQUESTOR                         VARCHAR2(128 CHAR)
USERID                            VARCHAR2(10 CHAR)
COST_CENTER                       VARCHAR2(4 CHAR)
SHIPPING_INSTRUCTIONS             SHIPPING_INSTRUCTIONS_T
SPECIAL_INSTRUCTIONS              VARCHAR2(2048 CHAR)
LINEITEMS                         LINEITEMS_T

SQL> SELECT COUNT(*)
  2 FROM purchaseorder;

  COUNT(*)
----------
       132

SQL> CREATE OR REPLACE VIEW purchaseorder_summary OF XMLType AS
  2  SELECT XMLQuery('copy $i := $p1 modify
  3  ((for $j in $i/PurchaseOrder/Actions
  4  return replace value of node $j with ()),
  5  (for $j in $i/PurchaseOrder/ShippingInstructions
  6  return replace value of node $j with ()),
  7  (for $j in $i/PurchaseOrder/LineItems
  8  return replace value of node $j with ()))
  9  return $i'
 10  PASSING OBJECT_VALUE AS "p1" RETURNING CONTENT)
 11  FROM purchaseorder p;

View created.

SQL> SELECT COUNT(*) FROM purchaseorder_summary;

  COUNT(*)
----------
       132

SQL> col xmlschema format a20
SQL> col schema_owner format a20
SQL> col element_name format a20
SQL> col view_type format a30

SQL> SELECT * FROM user_xml_views;

VIEW_NAME                      XMLSCHEMA            SCHEMA_OWNER         ELEMENT_NAME
------------------------------ -------------------- -------------------- --------------------
PURCHASEORDER_SUMMARY

SQL> SELECT view_name, view_type
  2  FROM user_views
  3* ORDER BY 1;

VIEW_NAME                      VIEW_TYPE
------------------------------ ------------------------------
ACCOUNT_MANAGERS
BOMBAY_INVENTORY
CUSTOMERS_VIEW
OC_CORPORATE_CUSTOMERS         CORPORATE_CUSTOMER_TYP
OC_CUSTOMERS                   CUSTOMER_TYP
OC_INVENTORIES                 INVENTORY_TYP
OC_ORDERS ORDER_TYP
OC_PRODUCT_INFORMATION         PRODUCT_INFORMATION_TYP
ORDERS_VIEW
PRODUCTS
PRODUCT_PRICES
PURCHASEORDER_SUMMARY          XMLTYPE
SYDNEY_INVENTORY
TORONTO_INVENTORY

SQL> SELECT *
  2  FROM purchaseorder_summary
  3  WHERE rownum = 1;

SYS_NC_ROWINFO$
---------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080
/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>JCHEN-20021009123338475PDT</Reference><Actions></Actions>
<Reject/><Requestor>John Z. Chen</Requestor><User>JCHEN</User>
<CostCenter>A10</CostCenter><ShippingInstructions></ShippingInstructions>
<SpecialInstructions>Counter to Counter</SpecialInstructions><LineItems></LineItems>
</PurchaseOrder>

SQL> SELECT OBJECT_VALUE FROM purchaseorder_summary
  2  WHERE XMLExists('$p/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]'
  3  PASSING OBJECT_VALUE AS "p");

OBJECT_VALUE
---------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080
/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>DAUSTIN-20021009123335811PDT</Reference><Actions></Actions>
<Reject/><Requestor>David L. Austin</Requestor><User>DAUSTIN</User>
<CostCenter>S30</CostCenter><ShippingInstructions></ShippingInstructions>
<SpecialInstructions>Courier</SpecialInstructions><LineItems></LineItems>
</PurchaseOrder>

Related Topics
Built-in Functions
Built-in Packages
Views
XMExists
XMLQuery
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved