Oracle XML VIEWs
Version 18c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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
XMLExists
XMLQuery
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx