SQL> conn jyang/jyang@athena
Connected.
SQL> 
SQL> select * from XMLTAB1;

SYS_NC_ROWINFO$                                                                 
--------------------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
paceSchemaLocation="http://www.oracle.com/xdb/po.xsd">                          
  <Reference>ADAMS-20011127121040988PST</Reference>                             
  <Actions>                                                                     
    <Action>                                                                    
      <User>SCOTT</User>                                                        
      <Date>2002-03-31</Date>                                                   
    </Action>                                                                   
  </Actions>                                                                    
  <Reject/>                                                                     
  <Requestor>Julie P. Adams</Requestor>                                         
  <User>ADAMS</User>                                                            
  <CostCenter>R20</CostCenter>                                                  
  <ShippingInstructions>                                                        
    <name>Julie P. Adams</name>                                                 
    <address>Redwood Shores, CA 94065</address>                                 
    <telephone>650 506 7300</telephone>                                         
  </ShippingInstructions>                                                       
  <SpecialInstructions>Ground</SpecialInstructions>                             
  <LineItems>                                                                   
    <LineItem ItemNumber="1">                                                   
      <Description>The Ruling Class</Description>                               
      <Part Id="715515012423" UnitPrice="39.95" Quantity="2"/>                  
    </LineItem>                                                                 
    <LineItem ItemNumber="2">                                                   
      <Description>Diabolique</Description>                                     
      <Part Id="037429135020" UnitPrice="29.95" Quantity="3"/>                  
    </LineItem>                                                                 
    <LineItem ItemNumber="3">                                                   
      <Description>8 1/2</Description>                                          
      <Part Id="037429135624" UnitPrice="39.95" Quantity="4"/>                  
    </LineItem>                                                                 
  </LineItems>                                                                  
</PurchaseOrder>                                                                
                                                                                

SQL> 
SQL> -- The existsNode function
SQL> -- Existing nodes
SQL> SELECT existsNode(value(X),'/PurchaseOrder/Reference')
  2     FROM XMLTAB1 X;

EXISTSNODE(VALUE(X),'/PURCHASEORDER/REFERENCE')                                 
-----------------------------------------------                                 
                                              1                                 

SQL> 
SQL> SELECT existsNode(value(X),
  2         '/PurchaseOrder[Reference="ADAMS-20011127121040988PST"]')
  3     FROM XMLTAB1 X;

EXISTSNODE(VALUE(X),'/PURCHASEORDER[REFERENCE="ADAMS-20011127121040988PST"]')   
-----------------------------------------------------------------------------   
                                                                            1   

SQL> 
SQL> SELECT existsNode(value(X),
  2         '/PurchaseOrder/LineItems/LineItem[2]/Part[@Id="037429135020"]')
  3     FROM XMLTAB1 X;

EXISTSNODE(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM[2]/PART[@ID="037429135020
--------------------------------------------------------------------------------
                                                                               1

SQL> 
SQL> SELECT existsNode(value(X),
  2         '/PurchaseOrder/LineItems/LineItem[Description="8 1/2"]')
  3     FROM XMLTAB1 X;

EXISTSNODE(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM[DESCRIPTION="81/2"]')    
----------------------------------------------------------------------------    
                                                                           1    

SQL> 
SQL> -- Non-existing nodes
SQL> SELECT existsNode(value(X),'/PurchaseOrder/UserName')
  2     FROM XMLTAB1 X;

EXISTSNODE(VALUE(X),'/PURCHASEORDER/USERNAME')                                  
----------------------------------------------                                  
                                             0                                  

SQL> 
SQL> SELECT existsNode(value(X),
  2         '/PurchaseOrder[Reference="ADAMS-XXXXXXXXXXXXXXXXXXXX"]')
  3     FROM XMLTAB1 X;

EXISTSNODE(VALUE(X),'/PURCHASEORDER[REFERENCE="ADAMS-XXXXXXXXXXXXXXXXXXXX"]')   
-----------------------------------------------------------------------------   
                                                                            0   

SQL> 
SQL> SELECT existsNode(value(X),
  2         '/PurchaseOrder/LineItems/LineItem[3]/Part[@Id="037429135020"]')
  3     FROM XMLTAB1 X;

EXISTSNODE(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM[3]/PART[@ID="037429135020
--------------------------------------------------------------------------------
                                                                               0

SQL> 
SQL> SELECT existsNode(value(X),
  2         '/PurchaseOrder/LineItems/LineItem[Description="Snow White"]')
  3     FROM XMLTAB1 X;

EXISTSNODE(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM[DESCRIPTION="SNOWWHITE"]'
--------------------------------------------------------------------------------
                                                                               0

SQL> 
SQL> -- The extractValue function
SQL> SELECT extractValue(value(x),'/PurchaseOrder/Reference')
  2    FROM XMLTAB1 X;

EXTRACTVALUE(VALUE(X),'/PURCHASEORDER/REFERENCE')                               
--------------------------------------------------------------------------------
ADAMS-20011127121040988PST                                                      

SQL> 
SQL> SELECT extractValue(value(x),
  2        '/PurchaseOrder/LineItems/LineItem[2]/Part/@Id')
  3    FROM XMLTAB1 X;

EXTRACTVALUE(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM[2]/PART/@ID')          
--------------------------------------------------------------------------------
037429135020                                                                    

SQL> 
SQL> SELECT extractValue(value(X),
  2                     '/PurchaseOrder/LineItems/LineItem/Description')
  3     FROM XMLTAB1 X;
   FROM XMLTAB1 X
        *
ERROR at line 3:
ORA-19025: EXTRACTVALUE returns value of only one node 


SQL> 
SQL> SELECT extractValue(value(X),
  2                     '/PurchaseOrder/LineItems/LineItem[1]')
  3    FROM XMLTAB1 X;
  FROM XMLTAB1 X
       *
ERROR at line 3:
ORA-19025: EXTRACTVALUE returns value of only one node 


SQL> 
SQL> -- Join XML table with other table
SQL> SELECT extractValue(value(x),'/PurchaseOrder/Reference')
  2      FROM XMLTAB1 X, SCOTT.EMP
  3      WHERE extractValue(value(x),'/PurchaseOrder/User') = EMP.ENAME
  4      AND EMP.EMPNO = 7876;

EXTRACTVALUE(VALUE(X),'/PURCHASEORDER/REFERENCE')                               
--------------------------------------------------------------------------------
ADAMS-20011127121040988PST                                                      

SQL> 
SQL> -- The extract method
SQL> -- Using extract() to Return an XML Fragment
SQL> SELECT extract(value(X),
  2                '/PurchaseOrder/LineItems/LineItem/Description')
  3    FROM XMLTAB1 X;

EXTRACT(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM/DESCRIPTION')               
--------------------------------------------------------------------------------
<Description>The Ruling Class</Description>                                     
<Description>Diabolique</Description>                                           
<Description>8 1/2</Description>                                                
                                                                                

SQL> 
SQL> -- Using extract() to Return a Node Tree that Matches an XPath Expression
SQL> SELECT extract(value(X),
  2               '/PurchaseOrder/LineItems/LineItem[1]')
  3    FROM XMLTAB1 X;

EXTRACT(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM[1]')                        
--------------------------------------------------------------------------------
<LineItem ItemNumber="1">                                                       
  <Description>The Ruling Class</Description>                                   
  <Part Id="715515012423" UnitPrice="39.95" Quantity="2"/>                      
</LineItem>                                                                     
                                                                                

SQL> 
SQL> -- The xmlsequence method
SQL> SELECT extractValue(value(t),'/Description')
  2    FROM XMLTAB1 X,
  3      TABLE ( xmlsequence (
  4             extract(value(X),
  5                   '/PurchaseOrder/LineItems/LineItem/Description')
  6             )
  7    ) t;

EXTRACTVALUE(VALUE(T),'/DESCRIPTION')                                           
--------------------------------------------------------------------------------
The Ruling Class                                                                
Diabolique                                                                      
8 1/2                                                                           

SQL> 
SQL> spool off