0311

The extract() function is similar to the existsnode() function. The extract() function extracts the node or a set of nodes from the document identified by the XPath expression and returns an XMLType instance containing an XML fragment. The extracted nodes can be elements, attributes, or text nodes. When extracted out, all text nodes are collapsed into a single text node value.

The extractvalue() function takes as arguments an XMLType instance and an XPath expression. It returns a scalar value corresponding to the result of the XPath evaluation on the XMLType instance. The extractvalue() tries to infer the proper return type from the XML schema of the document. If the XMLType is non-schema-based or the proper return type cannot be determined, Oracle XML DB returns a varchar2.

Let's have a look now at our SQL query, and then pick apart the text in Table 8-3.

1- select
extractvalue(p.PO_FILE, 'PurchaseOrder/ShippingInstructions/address')
2- from PURCHASE_ORDER p
3- where existsnode(p.PO_FILE,'/PurchaseOrder/User = ''SHAUNA"') = 1

The following is the expected result of our preceding SQL query:

EXTRACTVALUE(P.PO_FILE,'PURCHASEORDER/SHIPPINGINSTRUCTIONS/ADDRESS')
--------------------------------------------------------------------------------
249 Kracqueric Ave.
Ottawa
ON
K1A 1A1
Canada

Line	Important Points
1	The extractvalue() first argument is p.PO_FILE, the second is the XPath expression 'PurchaseOrder/ShippingInstructions/address'. The value at the defined location is returned, and its type will be inferred either by the associated XML Schema. In this case, it is varchar2.
2	The selection of data is being made against the PURCHASE_ORDER table.
3	The existsnode() first argument is p.PO_FILE, the second is the XPath expression '/PurchaseOrder/User = "SHAUNA"'. If the value is found at the defined location, then 1 is returned, signifying that the constraint is met.
TABLE 8-3. The Address XML SQL Query Definition

Team Fly

0311