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?
karsoods53
0
Newbie Poster
dilasing
0
Junior Poster in Training
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/db2/library/techarticle/dm-0611saracco/
karsoods53
0
Newbie Poster
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?
karsoods53
0
Newbie Poster
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"
dilasing
0
Junior Poster in Training
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.
dilasing
0
Junior Poster in Training
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.wss?rs=203&uid=swg27009230.
karsoods53
0
Newbie Poster
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?
dilasing
0
Junior Poster in Training
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.wss?rs=203&uid=swg27009230.
karsoods53
0
Newbie Poster
What are the main advantages of SQL/XML compared with XQuery language?
dilasing
0
Junior Poster in Training
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.
karsoods53
0
Newbie Poster
I need to use parameters in XQuery. Can anyone explain how to use parameters in XQuery with an example.
dilasing
0
Junior Poster in Training
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")
karsoods53
0
Newbie Poster
I heard that we can use XQuery FLWOR expressions in SQL/XML. Can you explain how we can use FLWOR expressions in SQL/XML?
dilasing
0
Junior Poster in Training
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[1] return $e'
passing contactinfo as "c")
from clients
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.
karsoods53
0
Newbie Poster
Can grouping and aggregation of XML be done using SQL/XML?
dilasing
0
Junior Poster in Training
Yes. It can be done.
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"
COLUMNS
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.
karsoods53
0
Newbie Poster
Can any one give syntax for getting both XML column data along with the normal relational data using a single query?
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.