If a table has both normal data and XML data, can indexing still be done?

Yes, in DB2 9, both XML indexes and relational indexes can coexist.

OK so what are the various types of XML indexes in pureXML?

There are 3 types of XML Indexes.

XML regions index is one such index which stores the locations of each XML document that is stored in XML storage area. There is one region index created by DB2 for each table having a XML column. This region index is automatically created and maintained by DB2.

The other 2 indexes are XML column path index and Index on an XML column.

XML column path index - This index is system-generated for each XML column created or added to the table. For example, if a table with two XML columns is created, there is one XML regions index, but two XML column path indexes generated by DB2 9.

Index on an XML column (XML index) - This index is created over an XML column. You can index every XML path in an XML column using XPath. Like other relational indexes, the XML index is created as a B-tree structure and stored in the same place as the relational indexes are stored. Multiple XML indexes can be defined in one XML column. Please check section 5.1 on http://www.redbooks.ibm.com/redbooks/pdfs/sg247298.pdf for information on this.

What is the use of the region index?

Region index is automatically created and maintained by DB2 for each table that has a XML column. Whenever the inserted XML document has a larger size than that of the database page size, DB2 automatically splits the XML document to fit it into multiple database pages. The region index is used to maintain the logical mapping between these split sub-documents. By splitting the XML document page-wise, DB2 is able to make use of the relational database facilities like prefetching.

How will the performance be if we index lot or most of the columns?

Like indexes on relational data, using indexes on an XML column to improve the query performance may have some cost. The performance for INSERT, UPDATE, and DELETE can decrease as the number of indexes defined on XML column increases. Indexes also take space, so you should only create indexes that are really necessary.

But how to create the XML index?

The CREATE INDEX statement has been enhanced to support XML indexing. XML indexes are created on columns of type XML based on path expressions (xmlpattern). When creating an XML index, the following fields are required:
Index name: Specify the name of an XML index.
Table and column names: Specify which XML column is indexed.
XMLPATTERN: Specify the node you want to index.
Data type: Specify the SQL data type for the XML index.

For eg. CREATE INDEX xidx1 on PRODUCT(XMLDOC)
GENERATE KEY USING XMLPATTERN
‘/products/product/id’ as SQL DOUBLE

This will create a XML index on the XMLDOC column of the PRODUCT table.
The index xidx1 will have all the product id values indexed with their data type being DOUBLE.

You can check section 5.1.2 on http://www.redbooks.ibm.com/redbooks/pdfs/sg247298.pdf link for more information on this.