XMLExtract() function
The XMLExtract() function finds the XML node or nodes that are specified by an XPath expression.
The extracted nodes can
be elements, attributes, or text nodes. You can use the
XMLExtract() function to extract the following
items:
- Numerical values on which you can create function-based indexes to speed up processing
- Collection expressions for use in the FROM clause of an SQL statement
- XML fragments to be combined into a single XML document
Syntax
The XMLExtract() function
has the following
syntax:
XML = XMLExtract(XML input, varchar XPath);
The input value
specifies the XML file from which to extract the node.
The XPath value specifies an XPath query
that specifies an
XML node within the XML file.
Returns
If this function finds more than one item, it returns only the first item. If the function does not find any items, it returns null.
Example
The following
example uses the XMLExtract() function to query
the value of
the Reference column for orders with SpecialInstructions set to
Rush:
SELECT XMLExtract(object_value,'/PurchaseOrder/Reference') "REFERENCE"
FROM PURCHASEORDER
WHERE
XMLExistsNode(object_value,'/PurchaseOrder[SpecialInstructions=“Rush“]
') = 1;
An example of a return value is as
follows:
<Reference>JSMITH-20021009123336271PDT</Reference>
<Reference>ABELL-20021009123336321PDT</Reference>
<Reference>JDOE-20021009123337303PDT</Reference>
<Reference>GWASHINGTON-20021009123337123PDT</Reference>