Hi,

I have made a table with one xml column of type XMLCLOB and stored an xml document in it. The xml document has multi occurance = YES for many of its elements. I want to extract the informasjon from the xml document which is stored in the xml column and I know that I have to use Table functions like ExtractIntegers. Is any body know the syntax for this? And anybody has an example for this?
The platform we are using is DB2 UDB 7.1 for OS/390.

Brgds
THava

I think it's best if you look at this xml-extender guide:

http://www-3.ibm.com/software/data/d...ETRIEVEELEMENT



Good luck.

I have done it but not for any help. It only describe how we can retrive informasjon that stored in XMLFILE data type.

The link I send You tells how to retrieve content and values of elements and attributes from xmlobj. The xmlobj can be XMLVARCHAR, XMLCLOB or XMLFILE. So you can use this function with yours xmlclob-type column. The syntax is:

db2xml.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:

* INTEGER
* SMALLINT
* DOUBLE
* REAL
* CHAR
* VARCHAR
* CLOB
* DATE
* TIME
* TIMESTAMP
* FILE

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:

* XMLVARCHAR
* XMLCLOB as LOCATOR
* XMLFILE

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.


EXAMPLE:

SELECT db2xml.extractVarchar(Order, '/Order/Customer/Name') from sales_order_view

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.


(I have used this succesfully with my db2 7.2 udb Linux)


Hope this help you

Sorry, the above example was for scalar-function. Here is one for table-function:

Table function: extractVarchars(xmlobj,'path')


Example for varchar -data type:

"select x.returnedvarchar from xml_table as xtb, table(db2xml.extractvarchars(xtb.xml_column, 'path')) as x where .... "

like:

db2 "select x.returnedvarchar from xml_table as xtb, table(db2xml.extractVarchars(xtb.xmlfile_column, '/XDocument/XElement')) as x where.."