Oracle XML DB : Storing and Processing XML Documents

原创 fizz 2022-12-02

199

Module1

Setup

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;

/

Module2

Querying XML Content

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
/

Module3

Updating XML Content

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"
       )
/

Module4

Creating and Querying Relational Views of XML Content

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

/

Module5

Creating and Querying XMLType Views of Relational Content

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

/

Module6

Generating XML Content from Relational Data

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 
/

Module7

Indexing XML Documents

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)
/

Module8

Reset


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;

/

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle XML DB : Storing and Processing XML Documents

Setup

Querying XML Content

Updating XML Content

Creating and Querying Relational Views of XML Content

Creating and Querying XMLType Views of Relational Content

Generating XML Content from Relational Data

Indexing XML Documents

Reset

评论