IBM Books

XML Extender Administration and Programming

Retrieving element contents and attribute values

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:

retrieved_datatype
Is the data type that is returned from the extracting function; it can be one of the following types:

xmlobj
Is the name of the XML column from which the element or attribute is to be extracted. This column must be defined as one of the following XML user-defined types:

path
Is the location path of the element or attribute in the XML document (such as /Order/Customer/Name). See Location path for location path syntax.

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


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