Let's first reset the tutorial by running following statement:
DECLARE
CURSOR gettable IS
SELECT table_name
FROM user_xml_tables
WHERE table_name IN ( 'PURCHASEORDER' );
BEGIN
FOR t IN gettable() LOOP
EXECUTE IMMEDIATE 'DROP TABLE "'|| t.table_name|| '" PURGE';
END LOOP;
END;
/
Now, please run the following statements to create an xmltype table and insert some xml documents.
DROP TABLE purchaseorder;
CREATE TABLE purchaseorder OF xmltype;
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO purchaseorder
VALUES ('<PurchaseOrder><Reference>ACABRIO-'
||i
||'PDT</Reference><Actions><Action><User>ACABRIO-'
||i
||
'</User></Action></Actions><Rejection/><Requestor>A. Cabrio '
|| i
||'</Requestor><User>ACABRIO-'
||i
||'</User><CostCenter>A'
||i
||'</CostCenter><ShippingInstructions><name>A. Cabrio '
||i
||'</name><Address><street>'
||i
||' Sporting Green Centre, Science Club, building '
||i
||', Magdalen</street><city>SFO-'
||i
||
'</city><state>CA</state><zipCode>99236</zipCode><country>United States of America</country></Address><telephone>269-'
||i
||
'-4036</telephone></ShippingInstructions><SpecialInstructions>Priority Overnight</SpecialInstructions><LineItems><LineItem ItemNumber="1"><Part Description="Face to Face: First Seven Years" UnitPrice="19.95">'
||i
||'</Part><Quantity>'
||i
||
'</Quantity></LineItem><LineItem ItemNumber="2"><Part Description="Runaway" UnitPrice="27.95">'
||i
||'</Part><Quantity>'
||i
||
'</Quantity></LineItem><LineItem ItemNumber="3"><Part Description="Founding Fathers: Men Who Shaped" UnitPrice="19.95">'
||i
||'</Part><Quantity>'
||i
||'</Quantity></LineItem></LineItems></PurchaseOrder>');
END LOOP;
COMMIT;
END;
/
There are many ways to query XML content in Oracle XML DB. We will see some basic examples here.
Q1. Getting the number of XML documents. There are many ways, following is one of them:
SELECT Count(*)
FROM purchaseorder p,
XMLTABLE('for $r in /PurchaseOrder return $r' passing object_value) t;
Q2. Retrieving the content of an XML document using pseudocolumn OBJECT_VALUE:
SELECT t.object_value.getclobval()
FROM purchaseorder t
WHERE rownum = 1;
Q3. Accessing fragments or nodes of an XML document:
SELECT Xmlquery('/PurchaseOrder/Reference' passing object_value returning
content)
FROM purchaseorder
WHERE ROWNUM <= 5
/
Q4. Accessing text node value:
SELECT xmlcast(xmlquery('$p/PurchaseOrder/Reference/text()' passing object_value AS "p" returning content) AS varchar2(30))
FROM purchaseorder
WHERE ROWNUM <= 5
/
Q5. Searching an xml document:
SELECT t.object_value.getclobval()
FROM purchaseorder t
WHERE xmlexists('/PurchaseOrder[Reference/text()=$REFERENCE]' passing object_value, 'ACABRIO-1PDT' AS "REFERENCE" );
Q6. Using XMLTABLE:
You can use XMLTable to perform SQL operations on a set of nodes that match an XQuery expression. XMLTable breaks up an XML fragment contained in an XMLType instance, inserts the collection-element data into a new, virtual table, which you can then query using SQL — in a join expression, for example. In particular, converting an XML fragment into a virtual table makes it easier to process the result of evaluating an XMLQuery expression that returns multiple nodes.
SELECT reference,
Count(*)
FROM purchaseorder,
xmltable('/PurchaseOrder' passing object_value columns reference varchar2(32) path 'Reference', lineitem xmltype path 'LineItems/LineItem'),
xmltable('LineItem' passing lineitem)
WHERE xmlexists('$p/PurchaseOrder' passing object_value AS "p")
AND ROWNUM <= 5
GROUP BY reference
ORDER BY reference;
XMLTABLE and XQuery (single predicate):
SELECT t.object_value.getclobval()
FROM purchaseorder p,
XMLTABLE('for $r in /PurchaseOrder[Reference/text()=$REFERENCE] return $r' passing object_value, 'ACABRIO-1PDT' AS
"REFERENCE") t;
XMLTABLE and XQuery (multiple predicates):
SELECT t.object_value.getclobval()
FROM purchaseorder p,
XMLTABLE(
'for $r in /PurchaseOrder[CostCenter=$CC or Requestor=$REQUESTOR or count(LineItems/LineItem) > $QUANTITY]/Reference return $r'
passing object_value, 'A1' AS "CC", 'A. Cabrio 10' AS "REQUESTOR", 0 AS
"QUANTITY") t
WHERE ROWNUM <= 5
/
Q7. Constructing a new summary document from the documents that match the specified predicates:
SELECT t.object_value.getclobval()
FROM Purchaseorder p,
XMLTable(
'<Summary>
{
for $r in /PurchaseOrder
return $r/Reference/text()
}
</Summary>'
passing object_value
) t
WHERE ROWNUM <= 5
/
Q8. Using XMLSerialize to format the XMLType and serialize it as a CLOB. Allows result to be viewed in products that do not support XMLType. XMLSerialize allows control over the layout of the serialized XML:
SELECT XMLSERIALIZE(CONTENT COLUMN_VALUE AS CLOB INDENT SIZE=2)
FROM Purchaseorder p,
XMLTable(
'<Summary>
{
for $r in /PurchaseOrder
return $r/Reference
}
</Summary>'
passing object_value
)
WHERE ROWNUM <= 5
/
Q9. Using XMLTable to create an in-line relational view from the documents that match the XQuery expression:
SELECT *
FROM Purchaseorder p,
XMLTable(
'for $r in /PurchaseOrder
for $l in $r/LineItems/LineItem
return
<Result ItemNumber="{fn:data($l/@ItemNumber)}">
{
$r/Reference,
$r/Requestor,
$r/User,
$r/CostCenter,
$l/Quantity
}
<Description>{fn:data($l/Part/@Description)}</Description>
<UnitPrice>{fn:data($l/Part/@UnitPrice)}</UnitPrice>
<PartNumber>{$l/Part/text()}</PartNumber>
</Result>'
passing object_value
columns
SEQUENCE for ordinality,
ITEM_NUMBER NUMBER(3) path '@ItemNumber',
REFERENCE VARCHAR2( 30) path 'Reference',
REQUESTOR VARCHAR2(128) path 'Requestor',
USERID VARCHAR2( 10) path 'User',
COSTCENTER VARCHAR2( 4) path 'CostCenter',
DESCRIPTION VARCHAR2(256) path 'Description',
PARTNO VARCHAR2( 14) path 'PartNumber',
QUANTITY NUMBER(12,4) path 'Quantity',
UNITPRICE NUMBER(14,2) path 'UnitPrice'
)
WHERE ROWNUM <= 5
/
Q10. Joining relational and XML tables using XQuery:
SELECT requestor,
department_name
FROM hr.employees e,
hr.departments d,
purchaseorder p,
XMLTABLE( 'for $r in /PurchaseOrder where $r/Reference=$REFERENCE or $r/User=$EMAIL return $r' passing object_value, 'ACABRIO-1PDT' AS
"REFERENCE", e.email AS "EMAIL" COLUMNS requestor path 'Requestor/text()' )
WHERE e.department_id = d.department_id
AND ROWNUM <= 5
/
You can update XML content, replacing either the entire contents of a document or only particular parts of a document. In a single operation, you can make multiple changes to a document. Each change uses an XQuery expression to identify a node to be updated, and specifies the new value for that node.
Let's first check the current state of the document:
SELECT xmlquery(
'<POSummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
$XML/PurchaseOrder/User,
$XML/PurchaseOrder/Requestor,
$XML/PurchaseOrder/LineItems/LineItem[2]
}
</POSummary>'
passing object_value AS "XML"
returning content
).getclobval() initial_state
FROM PURCHASEORDER
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value AS "XML",
'ACABRIO-100PDT' AS "REF"
)
/
UQ1. Modifying the content of existing nodes using XQuery update:
UPDATE PURCHASEORDER
SET object_value = XMLQuery
(
'copy $NEWXML := $XML modify (
for $PO in $NEWXML/PurchaseOrder return (
replace value of node $PO/User with $USERID,
replace value of node $PO/Requestor with $FULLNAME,
replace value of node $PO/LineItems/LineItem/Part[@Description=$OLDTITLE]/@Description with $NEWTITLE
)
)
return $NEWXML'
passing object_value as "XML",
'KCHUNG' as "USERID",
'Kelly Chung' as "FULLNAME",
'Runaway' as "OLDTITLE",
'Runaway[Updated]' as "NEWTITLE"
returning content
)
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]/LineItems/LineItem/Part[@Description=$OLDTITLE]'
passing object_value as "XML",
'ACABRIO-100PDT' as "REF",
'Runaway' as "OLDTITLE"
)
/
Checking updated document:
SELECT XMLQUERY(
'<POSummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
$XML/PurchaseOrder/User,
$XML/PurchaseOrder/Requestor,
$XML/PurchaseOrder/LineItems/LineItem[2]
}
</POSummary>'
passing object_value as "XML"
returning CONTENT
).getclobval() UPDATED_NODES
FROM PURCHASEORDER
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'ACABRIO-100PDT' as "REF"
)
/
UQ2. Deleting a node using XQuery update:
UPDATE PURCHASEORDER
SET object_value = XMLQuery(
'copy $NEWXML := $XML modify (
delete nodes $NEWXML/PurchaseOrder/LineItems/LineItem[@ItemNumber=$ITEMNO]
)
return $NEWXML'
passing object_value as "XML", 2 as ITEMNO
returning CONTENT
)
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'ACABRIO-100PDT' as "REF"
)
/
Checking updated document:
SELECT XMLQUERY(
'<POSummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
$XML/PurchaseOrder/LineItems/LineItem[2]
}
</POSummary>'
passing object_value as "XML"
returning CONTENT
).getclobval() DELETED_NODE
FROM PURCHASEORDER
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'ACABRIO-100PDT' as "REF"
)
/
UQ3. Inserting a node using XQuery update:
UPDATE PURCHASEORDER
SET object_value = XMLQuery(
'copy $NEWXML := $XML modify (
for $TARGET in $NEWXML/PurchaseOrder/LineItems/LineItem[@ItemNumber="3"]
return insert node $LINEITEM after $TARGET
)
return $NEWXML'
passing object_value as "XML",
xmlType(
'<LineItem ItemNumber="4">
<Part Description="Rififi" UnitPrice="29.95">37429155622</Part>
<Quantity>2</Quantity>
</LineItem>'
) as "LINEITEM"
returning CONTENT
)
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'ACABRIO-100PDT' as "REF"
)
/
Checking updated document:
SELECT XMLQUERY(
'<POSummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
$XML/PurchaseOrder/LineItems/LineItem[3]
}
</POSummary>'
passing object_value as "XML"
returning CONTENT
).getclobval() INSERTED_NODE
FROM PURCHASEORDER
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'ACABRIO-100PDT' as "REF"
)
/
UQ4. Undo all the above changes using XQuery update:
UPDATE PURCHASEORDER
SET object_value = XMLQuery(
' copy $NEWXML := $XML modify (
for $PO in $NEWXML/PurchaseOrder return (
replace value of node $PO/User with $USERID,
replace value of node $PO/Requestor with $FULLNAME,
replace node $PO/LineItems with $LINEITEMS
)
)
return $NEWXML'
passing object_value as "XML",
'ACABRIO-100' as "USERID",
'A. Cabrio 100' as "FULLNAME",
xmlType(
'<LineItems>
<LineItem ItemNumber="1">
<Part Description="Face to Face: First Seven Years" UnitPrice="19.95">100</Part>
<Quantity>100</Quantity>
</LineItem>
<LineItem ItemNumber="2">
<Part Description="Runaway" UnitPrice="27.95">100</Part>
<Quantity>100</Quantity>
</LineItem>
<LineItem ItemNumber="3">
<Part Description="Founding Fathers: Men Who Shaped" UnitPrice="19.95">100</Part>
<Quantity>100</Quantity>
</LineItem>
</LineItems>'
) as "LINEITEMS"
returning content
)
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'ACABRIO-100PDT' as "REF"
)
/
Checking updated document:
SELECT XMLQUERY(
'<POSummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
$XML/PurchaseOrder/User,
$XML/PurchaseOrder/Requestor,
$XML/PurchaseOrder/LineItems/LineItem[2]
}
</POSummary>'
passing object_value as "XML"
returning CONTENT
).getclobval() FINAL_STATE
FROM PURCHASEORDER
WHERE xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'ACABRIO-100PDT' as "REF"
)
/
Creating Relational Views
V1. Creating a Master View, from elements that occur at most once per document:
CREATE OR replace VIEW purchaseorder_master_view
AS
SELECT m.*
FROM purchaseorder p,
XMLTABLE ( '$p/PurchaseOrder' passing p.object_value AS "p" COLUMNS
reference
path 'Reference/text()', requestor path 'Requestor/text()', userid path
'User/text()', costcenter path 'CostCenter/text()', ship_to_name path
'ShippingInstructions/name/text()', ship_to_street path
'ShippingInstructions/Address/street/text()', ship_to_city path
'ShippingInstructions/Address/city/text()', ship_to_county path
'ShippingInstructions/Address/county/text()', ship_to_postcode path
'ShippingInstructions/Address/postcode/text()', ship_to_state path
'ShippingInstructions/Address/state/text()', ship_to_province path
'ShippingInstructions/Address/province/text()', ship_to_zip path
'ShippingInstructions/Address/zipCode/text()', ship_to_country path
'ShippingInstructions/Address/country/text()', ship_to_phone path
'ShippingInstructions/telephone/text()', instructions path
'SpecialInstructions/text()' ) m
/
V2. Creating a Detail View, from the contents of the LineItem collection. LineItem can occur more than once is a document. The rows in this view can be joined with the rows in the previous view using REFERENCE, which is common to both views.
CREATE OR replace VIEW purchaseorder_detail_view
AS
SELECT m.reference,
l.*
FROM purchaseorder p,
XMLTABLE ( '$p/PurchaseOrder' passing p.object_value AS "p" COLUMNS
reference
path 'Reference/text()', lineitems xmltype path 'LineItems/LineItem' )
m,
XMLTABLE ( '$l/LineItem' passing m.lineitems AS "l" COLUMNS itemno path
'@ItemNumber', description path 'Part/@Description', partno path
'Part/text()',
quantity path 'Quantity', unitprice path 'Part/@UnitPrice' ) l
/
Querying Over Relational Views
Q1. Execute a simple SQL query over the relational view of XML content showing the use of SQL Group By. Note- XQuery 1.0 does not support the concept of group by.
SELECT costcenter,
Count(*)
FROM purchaseorder_master_view
WHERE ROWNUM <= 5
GROUP BY costcenter
/
Q2. A simple Query showing a join between the master and detail views with relational predicates on both views.
SELECT m.reference,
instructions,
itemno,
partno,
description,
quantity,
unitprice
FROM purchaseorder_master_view m,
purchaseorder_detail_view d
WHERE m.reference = d.reference
AND m.requestor = 'A. Cabrio 1'
AND d.quantity > 0
AND D.unitprice > 17.00
AND ROWNUM <= 5
/
Q3. A simple Query showing a join between the master and detail views with relational predicate on detail view.
SELECT M.reference,
L.itemno,
L.partno,
L.description
FROM purchaseorder_master_view m,
purchaseorder_detail_view l
WHERE M.reference = L.reference
AND l.partno = '1'
AND ROWNUM <= 5
/
Q4. A SQL Query on detail view making use of SQL Analytics functionality not provided by XQuery. The Group by extension ROLLUP function enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions, as well as a grand total.
SELECT partno,
Count(*) "Orders",
quantity "Copies"
FROM purchaseorder_detail_view
WHERE partno = '1'
GROUP BY rollup( partno, quantity )
/
Q5. A SQL Query on detail view making use of SQL Analytics functionality not provided by XQuery. The analytic function LAG provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.
SELECT partno,
reference,
quantity,
quantity - Lag(quantity, 1, quantity)
over (
ORDER BY Substr(reference, Instr(reference, '-') + 1)) AS
DIFFERENCE
FROM purchaseorder_detail_view
WHERE partno = '1'
ORDER BY Substr(reference, Instr(reference, '-') + 1) DESC
/
Creating XMLType Views
Creating a persistant XML view of relational content:
CREATE OR replace VIEW DEPARTMENT_XML of xmltype
with object id
(
XMLCAST(XMLQUERY('/Department/Name' passing OBJECT_VALUE returning CONTENT) as VARCHAR2(30))
)
as
SELECT xmlElement
(
"Department",
xmlAttributes( d.DEPARTMENT_ID as "DepartmentId"),
xmlElement("Name", d.DEPARTMENT_NAME),
xmlElement
(
"Location",
xmlForest
(
STREET_ADDRESS as "Address", CITY as "City", STATE_PROVINCE as "State",
POSTAL_CODE as "Zip",COUNTRY_NAME as "Country"
)
),
xmlElement
(
"EmployeeList",
(
select xmlAgg
(
xmlElement
(
"Employee",
xmlAttributes ( e.EMPLOYEE_ID as "employeeNumber" ),
xmlForest
(
e.FIRST_NAME as "FirstName", e.LAST_NAME as "LastName", e.EMAIL as "EmailAddress",
e.PHONE_NUMBER as "Telephone", e.HIRE_DATE as "StartDate", j.JOB_TITLE as "JobTitle",
e.SALARY as "Salary", m.FIRST_NAME || ' ' || m.LAST_NAME as "Manager"
),
xmlElement ( "Commission", e.COMMISSION_PCT )
)
)
from HR.EMPLOYEES e, HR.EMPLOYEES m, HR.JOBS j
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
and j.JOB_ID = e.JOB_ID
and m.EMPLOYEE_ID = e.MANAGER_ID
)
)
) as XML
FROM HR.DEPARTMENTS d, HR.COUNTRIES c, HR.LOCATIONS l
WHERE d.LOCATION_ID = l.LOCATION_ID and
l.COUNTRY_ID = c.COUNTRY_ID
/
Querying Over XMLType Views
Now let's execute XQueries over XMLType views created above.
Q1. Returning department named "Executive":
SELECT T.object_value.getclobval()
FROM department_xml D,
XMLTABLE ( 'for $r in /Department[Name="Executive"] return $r' passing object_value ) T
/
Q2. Returning departments having employee with last name "Grant":
SELECT T.object_value.getclobval()
FROM department_xml D,
XMLTABLE ( 'for $r in /Department[EmployeeList/Employee/LastName="Grant"]/Name return $r' passing object_value ) T
/
You can use standard SQL/XML functions to generate one or more XML documents.
Using XQUERY
Q1. An XQuery showing how fn:collection can be used to generate a simple XML document from each row in a relational table (i.e. DEPARTMENTS here).
SELECT xmlserialize(document column_value AS clob indent size=2)
FROM xmltable ( 'fn:collection("oradb:/HR/DEPARTMENTS")' )
WHERE rownum < 3
/
Q2. Using XQuery and fn:collection to create XML documents from more than one relational table.
SELECT D.object_value.getclobval()
FROM XMLTable
(
'for $d in fn:collection("oradb:/HR/DEPARTMENTS")/ROW,
$l in fn:collection("oradb:/HR/LOCATIONS")/ROW,
$c in fn:collection("oradb:/HR/COUNTRIES")/ROW
where $d/LOCATION_ID = $l/LOCATION_ID
and $l/COUNTRY_ID = $c/COUNTRY_ID
return
<Department DepartmentId= "{$d/DEPARTMENT_ID/text()}" >
<Name>{$d/DEPARTMENT_NAME/text()}</Name>
<Location>
<Address xsi:type="address_DE">{$l/STREET_ADDRESS/text()}</Address>
<City>{$l/CITY/text()}</City>
<State>{$l/STATE_PROVINCE/text()}</State>
<Zip>{$l/POSTAL_CODE/text()}</Zip>
<Country>{$c/COUNTRY_NAME/text()}</Country>
</Location>
<EmployeeList>
{
for $e in fn:collection("oradb:/HR/EMPLOYEES")/ROW,
$m in fn:collection("oradb:/HR/EMPLOYEES")/ROW,
$j in fn:collection("oradb:/HR/JOBS")/ROW
where $e/DEPARTMENT_ID = $d/DEPARTMENT_ID
and $j/JOB_ID = $e/JOB_ID
and $m/EMPLOYEE_ID = $e/MANAGER_ID
return
<Employee employeeNumber="{$e/EMPLOYEE_ID/text()}" >
<FirstName>{$e/FIRST_NAME/text()}</FirstName>
<LastName>{$e/LAST_NAME/text()}</LastName>
<EmailAddress>{$e/EMAIL/text()}</EmailAddress>
<Telephone>{$e/PHONE_NUMBER/text()}</Telephone>
<StartDate>{$e/HIRE_DATE/text()}</StartDate>
<JobTitle>{$j/JOB_TITLE/text()}</JobTitle>
<Salary>{$e/SALARY/text()}</Salary>
<Manager>{$m/LAST_NAME/text(), ", ", $m/FIRST_NAME/text()}</Manager>
<Commission>{$e/COMMISSION_PCT/text()}</Commission>
</Employee>
}
</EmployeeList>
</Department>'
) D
WHERE rownum < 3
/
Using SQLXML
Generating XML data from DEPARTMENTS, HR.COUNTRIES c, HR.LOCATIONS relational tables:
SELECT xmlelement ( "Department", xmlattributes( d.department_id as "DepartmentId"), xmlelement("Name", d.department_name), xmlelement ( "Location", xmlforest ( street_address AS "Address", city AS "City", state_province AS "State", postal_code AS "Zip",country_name AS "Country" ) ), xmlelement ( "EmployeeList",
(
SELECT xmlagg ( xmlelement ( "Employee", xmlattributes ( e.employee_id AS "employeeNumber" ), xmlforest ( e.first_name AS "FirstName", e.last_name AS "LastName", e.email AS "EmailAddress", e.phone_number AS "Telephone", e.hire_date AS "StartDate", j.job_title AS "JobTitle", e.salary AS "Salary", m.first_name
|| ' '
|| m.last_name AS "Manager" ), xmlelement ( "Commission", e.commission_pct ) ) )
FROM hr.employees e,
hr.employees m,
hr.jobs j
WHERE e.department_id = d.department_id
AND j.job_id = e.job_id
AND m.employee_id = e.manager_id ) ) ).getclobval() AS xml
FROM hr.departments d,
hr.countries c,
hr.locations l
WHERE d.location_id = l.location_id
AND l.country_id = c.country_id
AND rownum < 3
/
Create a complete XML Index on the PURCHASEORDER table
CREATE INDEX PURCHASEORDER_IDX
on PURCHASEORDER (OBJECT_VALUE)
indexType is xdb.xmlIndex
/
A set of simple queries to demonstrate how indexing can optimize XQuery operations.
Q1.
SELECT t.object_value.getclobval()
FROM purchaseorder p,
XMLTABLE('for $r in /PurchaseOrder[Reference/text()=$REFERENCE] return $r' passing object_value, 'ACABRIO-1PDT' AS
"REFERENCE") t
/
Q2.
SELECT t.object_value.getclobval()
FROM purchaseorder p,
XMLTABLE('for $r in /PurchaseOrder[User/text()=$USER] return $r' passing object_value, 'ACABRIO-1' AS
"USER") t
/
Q3.
SELECT t.object_value.getclobval()
FROM purchaseorder p,
XMLTABLE('for $r in /PurchaseOrder[LineItems/LineItem[Part/text()=$UPC]] return $r' passing object_value, '1' AS "UPC") t
/
Q4.
SELECT t.object_value.getclobval()
FROM purchaseorder p,
XMLTABLE('for $r in /PurchaseOrder[LineItems/LineItem[Part/text() = $UPC or Quantity > $QUANTITY]] return $r' passing object_value, '1' AS "UPC", 0
AS
"QUANTITY") t
WHERE ROWNUM <= 5
/
Path-Subsetted XML Index
DROP INDEX purchaseorder_idx
/
CREATE INDEX PURCHASEORDER_IDX
on PURCHASEORDER (OBJECT_VALUE)
indextype is XDB.XMLINDEX
parameters (
'paths (
include (
/PurchaseOrder/Reference
/PurchaseOrder/LineItems/LineItem/Part/* ))'
)
/
Structured XML Index
DROP INDEX PURCHASEORDER_IDX
/
BEGIN
dbms_xmlindex.Dropparameter('PO_SXI_PARAMETERS');
END;
/
begin
DBMS_XMLINDEX.registerParameter(
'PO_SXI_PARAMETERS',
'GROUP PO_LINEITEM
xmlTable PO_INDEX_MASTER ''/PurchaseOrder''
COLUMNS
REFERENCE varchar2(30) PATH ''Reference/text()'',
LINEITEM xmlType PATH ''LineItems/LineItem''
VIRTUAL xmlTable PO_INDEX_LINEITEM ''/LineItem''
PASSING lineitem
COLUMNS
ITEMNO number(38) PATH ''@ItemNumber'',
UPC varchar2(14) PATH ''Part/text()'',
DESCRIPTION varchar2(256) PATH ''Part/@Description''
');
end;
/
CREATE INDEX PURCHASEORDER_IDX
on PURCHASEORDER (OBJECT_VALUE)
indextype is XDB.XMLINDEX
parameters ('PARAM PO_SXI_PARAMETERS')
/
CREATE UNIQUE INDEX REFERENCE_IDX
on PO_INDEX_MASTER (REFERENCE)
/
CREATE INDEX UPC_IDX
on PO_INDEX_LINEITEM (UPC)
/