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")
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.DB2Driver.connect (DB2Driver.java:178)
These are my JNDI details:
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
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
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'
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.
DB2 9 XQuery language does not support external parameters. However, SQL/XML functions -XMLQUERY, XMLTABLE, and XMLEXISTS do support them. They allow you to pass SQL parameters markers as a variable into the embedded XQuery expressions.
Below example SQL/XML query with parameter markers:
select info from customer where xmlexists('$i/customerinfo[phone = $p]' passing info as "i", cast(? as varchar(12)) as "p")
We can include XQuery in our SQL/XML queries. XQueries may contain some or all of the following clauses: "for," "let," "where," "order by", and "return." Collectively, they form FLWOR (pronounced flower) expressions. SQL/XML programmers may find it convenient to incorporate XQueries into their SELECT lists to extract (or project) fragments of XML documents into their result sets.
Let us imagine that you want to retrieve the names and primary email addresses of your "Gold" customers. Here, you pass XQuery (with "for" and "return" clauses) as input to the xmlquery function:
select name, xmlquery('for $e in $c/Client/email return $e'
passing contactinfo as "c")
where status = 'Gold'
The first line specifies that customer names and output from the XMLQuery function will be included in the result set, and indicates that the first "email" sub-element of the "Client" element is to be returned. The second line identifies the source of our XML data -- the "contactinfo" column. Line 3 tells us that this column is in the "clients" table. Finally, the fourth line indicates that only "Gold" customers are of interest to us.
As an example, Let us count employees grouped by office, in other words, the number of employees in each office. It is easier to use SQL/XML functions, such as XMLTABLE or XMLQUERY to extract the data items from XML columns and then use familiar SQL concepts to express grouping and aggregation on top of that.
Consider below mentioned query:
select X.office, count(X.emp)
from dept, XMLTABLE ('$d/dept/employee' passing deptdoc as "d"
emp VARCHAR(30) PATH 'name',
office INTEGER PATH 'office ') as X
GROUP BY X.office;
In above query the XMLTABLE function extracts /dept/employee/name and /dept/employee/office from every document in form of a table with two columns named "emp" and "office". Using SQL group by and aggregation functions on that table is usually more efficient than producing the same result in plain XQuery.