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.