ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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 *
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");
