IBM Books

XML Extender Administration and Programming

extractVarchar() and extractVarchars()

Purpose

Extracts the element content or attribute value from an XML document and returns the data as VARCHAR type.

Syntax
Scalar function
>>-extractVarchar----(--xmlobj--,--path--)---------------------><
 
Table function
>>-extractVarchars----(--xmlobj--,--path--)--------------------><
 

Parameters

Table 34. extractVarchar and extractVarchars function parameters
Parameter Data type Description
xmlobj
XMLVARCHAR,
XMLFILE, or
XMLCLOB

The column name.
path VARCHAR The location path of the element or attribute.

Return type

VARCHAR(4K)

Returned column name (table function)

returnedVarchar

Example

In a database with more than 1000 XML documents that are stored in the column ORDER in the SALES_TAB table, you might want to find all the customers who have ordered items that have an ExtendedPrice greater than 2500.00. The following SQL statement uses the extracting UDF in the SELECT clause:

SELECT extractVarchar(Order, '/Order/Customer/Name') from sales_order_view
   WHERE price > 2500.00

The UDF extractVarchar() takes the column ORDER as the input and the location path /Order/Customer/Name as the select identifier. The UDF returns the names of the customers. With the WHERE clause, the extracting function evaluates only those orders with an ExtendedPrice greater than 2500.00.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]