| 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
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"
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
SELECT COUNT(*)
FROM purchaseorder;
COUNT(*)
----------
132
CREATE OR REPLACE VIEW
purchaseorder_summary OF sys.xmltype AS
SELECT XMLQuery('copy $i := $p1 modify (
(for $j in $i/PurchaseOrder/Actions
return replace value of node $j with ()),
(for $j in $i/PurchaseOrder/ShippingInstructions
return replace value of node $j with ()),
(for $j in $i/PurchaseOrder/LineItems
return replace value of node $j with ()))
return $i'
PASSING OBJECT_VALUE AS "p1" RETURNING CONTENT)
FROM purchaseorder p;
View created.
SELECT COUNT(*) FROM purchaseorder_summary;
COUNT(*)
----------
132
col xmlschema format a20
col schema_owner format a20
col element_name format a20
col view_type format a30
SELECT * FROM user_xml_views;
VIEW_NAME
XMLSCHEMA
SCHEMA_OWNER ELEMENT_NAME
--------------------------- -------------------- --------------------
----------------
PURCHASEORDER_SUMMARY
SELECT view_name, view_type
FROM user_views
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
SELECT *
FROM purchaseorder_summary
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>
SELECT OBJECT_VALUE
FROM purchaseorder_summary
WHERE XMLExists('$p/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]'
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> |