I have looked at the syntax of SQL/XML query and tried to run a sample query. But not able to write my query using it. Can someone provide a tested and working SQL/XML?

Check the query below. I have tested this and it works perfectly.

select xmlquery('declare default element namespace
"urn:xmlns:bol-listing:business1.0"; $b/businessesname' passing XMLINFO as "b") from testtable where xmlexists('declare default element namespace "urn:xmlns:bol-listing:business1.0"; $b/businessesname' passing XMLINFO as "b")

You can find more information at
http://www.ibm.com/developerworks/db...m-0611saracco/

I am facing JNDI problem with tomcat server, when I use a JNDI definition to connect DB2 database, always it’s throwing below exception, I have added db2jcc.jar jar file into tomcat common lib. Can you advise how to fix this problem?

The problem is:
com.ibm.db2.jcc.b.SqlException: Failure in loading T2 native library db2jcct2
at com.ibm.db2.jcc.t2.a.a(a.java:31)
at com.ibm.db2.jcc.t2.T2Configuration.<clinit>(T2Configuration.java:70)
at com.ibm.db2.jcc.DB2Driver.connect (DB2Driver.java:178)
at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)

These are my JNDI details:
name="jdbc/sample"
type="javax.sql.DataSource"
maxActive="10"
maxIdle="15"
maxWait="10000"
username="xxxxx"
password="xxxxx"
driverClassName="com.ibm.db2.jcc.DB2Driver"
url="jdbc:db2:sample"

You are using type 2 driver, so probably a .so or .dll files are missing. (db2jcct2.dll or libdb2jcct2.so if you are running Linux)

Set the LD_LIBRARY_PATH to the directory of native libraries.
export LD_LIBRARY_PATH=/opt/ibm/db2/V9.0/lib32/
Or can be different on your system.

If you are using type 2 jdbc db2 driver you should have a db2 client installed.
If you are using type 4 driver you just specify jdbc url like: jdbc: db2://server-ip-address:50000/sample?user=xxxx& password=xxxx

MTK can be downloaded at
http://www-306.ibm.com/software/data/db2/migration/mtk/. More information can be found at
http://www-1.ibm.com/support/docview...id=swg27009230.

I am working on a project and I need to get relational data from DB2 9 XML columns, how do we convert XML data with namespaces into relational data?

Use SQL/XML() function to dynamically convert XML data into SQL data types such as varchar, int, date. The presence of namespaces in the XML data will affect you write these queries. below example retrieves information about business, if you xmlinfo like below:

<business><name>Apollo</name><contact><address>Tol street, Black
stone</address><state>MA</state><city>boston</city><zip>02913</zip></contact></business><business><name>BW
Pizza Hut</name><contact><address>North
street</address><state>MA</state><city>boston</city><zip>02113</zip></contact></business>


select mb.id, b.name, b.address, b.state, b.city, b.zip from BUSINESSES mb, xmltable('declare namespace ns="urn:xmlns:tol-listing:business1.0"; $b/ns:business' passing XMLINFO as "b" columns "NAME" varchar(120) path 'declare namespace ns="urn:xmlns:tol-listing:business1.0";ns:name', "ADDRESS" varchar(10) path '*:contact/*:address', "STATE" varchar(10) path '*:contact/*:state', "CITY" varchar(60) path '*:contact/*:city',
"ZIP" varchar(5) path '*:contact/*:zip') as b where mb.id = '3hy4b20xz7u54s2'

MTK can be downloaded at
http://www-306.ibm.com/software/data/db2/migration/mtk/. More information can be found at
http://www-1.ibm.com/support/docview...id=swg27009230.

What are the main advantages of SQL/XML compared with XQuery language?

SQL/XML scores over XQuery in below areas:

i) If your query need to return data from relational columns and from XML columns at the same time.
ii) If your query require full-text search conditions.
iii) If you want results returned as sets and missing XML elements represented with nulls.
iv) If you want to use parameter markers, because XQuery does not supports external parameters.
v) SQL/XML is good for applications that need to integrate relational and XML data. It provides the easiest means to join XML data and relational data.
vi) SQL/XML is good for grouping and aggregating of XML. The XQuery language does not provide an explicit group-by construct. Although grouping and aggregation can be expressed in XQuery using self-joins, it is quite awkward.