You can retrieve (extract) the content of an element or an attribute value from one or more XML documents (single document or collection document search). The XML Extender provides user-defined extracting functions that you can specify in the SQL SELECT clause for each of the SQL data types.
Retrieving the content and values of elements and attributes is useful in developing your applications, because you can access XML data as relational data. For example, you might have 1000 XML documents that are stored in the column ORDER in the table SALES_TAB. You can retrieve the names of all customers who have ordered items using the following SQL statement with the extracting UDF in the SELECT clause to retrieve this information:
SELECT extractVarchar(Order, '/Order/Customer/Name') from sales_order_view WHERE price > 2500.00
In this example, the extracting UDF retrieves the element <customer> from the column ORDER as a VARCHAR data type. The location path is /Order/Customer/Name (see Location path for location path syntax).. Additionally, the number of returned values is reduced by using a WHERE clause, which specifyies that only the contents of the <customer> element with a subelement <ExtendedPrice> has a value greater than 2500.00.
To extract element content or attribute values: Use the extracting UDFs listed in Table 13 by using the following syntax as either table or scalar functions:
extractretrieved_datatype(xmlobj, path)
Where:
Table 13 shows the extracting functions, both in scalar and table
format:
Table 13. The XML Extender extracting functions
Scalar function | Table function | Returned column name (table function) | Return type |
---|---|---|---|
extractInteger() | extractIntegers() | returnedInteger | INTEGER |
extractSmallint() | extractSmallints() | returnedSmallint | SMALLINT |
extractDouble() | extractDoubles() | returnedDouble | DOUBLE |
extractReal() | extractReals() | returnedReal | REAL |
extractChar() | extractChars() | returnedChar | CHAR |
extractVarchar() | extractVarchars() | returnedVarchar | VARCHAR |
extractCLOB() | extractCLOBs() | returnedCLOB | CLOB |
extractDate() | extractDates() | returnedDate | DATE |
extractTime() | extractTimes() | returnedTime | TIME |
extractTimestamp() | extractTimestamps() | returnedTimestamp | TIMESTAMP |