XMLExtractValue() function

The XMLExtractValue() function extracts the actual (scalar) value from an XML object that you specify by using the XPath parameter. The result of the XPath query must be a single node and must be an element, a text node, or an attribute.

If you want a specific data type, you can wrap the XMLExtractValue() function with a conversion function, such as a function that converts the varchar to a date.

Syntax

The XMLExtractValue() function has the following syntax:

varchar = XMLExtractValue(XML input, varchar XPath);

The input value specifies an XML file.

The XPath value specifies an XPath query.

Returns

If the result is an element, it must have a single text node as its child; the child node provides the text content for the scalar return value. If the node does not exist, the function returns null. If the XPath expression returns more than one node or if the expression points to an element node with anything other than a single text child node, the function returns an error.

Example

The following query extracts the scalar value of the Reference column:

SELECT XMLExtractValue(object_value,'/PurchaseOrder/Reference') 
"REFERENCE"
FROM PURCHASEORDER
WHERE 
XMLExistsNode(object_value,'/PurchaseOrder[SpecialInstructions=“Rush“]
') = 1;

An example of a possible return value follows. This return value differs from the return value for the similar example for the XMLExtract() function. In that example, each line of data is wrapped with a <Reference> element. In the following example, just the scalar value is extracted and returned:

JSMITH-20021009123336271PDT
ABELL-20021009123336321PDT
JDOE-20021009123337303PDT
GWASHINGTON-20021009123337123PDT