How can I Query the XML data from the database?

DB2 9 supports query languages familiar to both relational and XML programmers. The XML data can be obtained using SQL or XQuery.

What is XQuery? Is this different from SQL?

XQuery is a new query language that supports navigational (or path-based) expressions. In fact, your applications can employ freely statements from both query languages, and a single query statement can actually incorporate both SQL and XQuery. The results of queries can return data in relational and XML formats, or a combination of both.

XQuery differs from SQL in a number of key respects, largely because the languages were designed to work with different data models that have different characteristics. XQuery supports path expressions to enable programmers to navigate through XML's hierarchical structure, while plain SQL (without XML extensions) does not. XQuery supports both typed and untyped data, while SQL data is always defined with a specific type. XQuery lacks null values because XML documents omit missing or unknown data. SQL, of course, uses nulls to represent missing or unknown data values. XQuery returns sequences of XML data; SQL returns result sets of various SQL data types.

Why should we use XQuery when we already have SQL? Is there any advantage in learning XQuery and using it?

We can query data in DB2 XML columns using SQL or SQL with XML extensions (SQL/XML). Unfortunately, there's a lot you can't do with just SQL. Plain SQL statements enable you to retrieve full XML documents, but you can't retrieve partial XML documents or specific element values from an XML document. You need to use SQL with XML extensions (SQL/XML), XQuery, or a combination of both to retrieve partial XML documents. Xquery can offer powerful capabilities for searching and integrating traditional SQL and XML data. DB2 also supports bilingual queries -- that is, queries that combine expressions from both SQL and XQuery. Which language (or combination of languages) you should use depends on the application needs. You can check http://www.ibm.com/developerworks/db2/library/techarticle/dm-0604saracco/ for more information about querying XML data using Xquery.

I have been using SQL queries all through. Can I just use SQL queries without having to learn Xquery?

When should I use XQuery with embedded SQL?

XQuery with embedded SQL can be a good choice if you want to leverage relational predicates and indexes as well as full-text search to pre-filter the documents from an XML column which are then input to an XQuery. SQL embedded in XQuery also allows you to run external functions on the XML columns. But, if you need to perform data analysis queries with grouping and aggregations, you may prefer SQL/XML.

I have heard of the pureXML feature of DB2 9 and want to switch to DB2 9. Can someone tell more about DB2 9 and PureXML?

DB2 9 is a hybrid database system. It can have both relational data and native XML data in the same database. DB2 9 introduces an XML data type. The relational data is stored in tabular structures and the XML data is stored in tree structure. The structure enables XML data to be stored in its hierarchical form within columns of a table. Because data is stored in its native form, both types of data can benefit from the performance. On top of both tabular structures and tree structures, there is one hybrid database engine that processes both types of data. There are two different parsers to process SQL and XQuery. A single compiler is used for both languages. DB2 9’s compiler and optimizer can handle both languages. An application can use the combinations of SQL and XQuery to access relational and XML data in the hybrid database. DB2 9 treats XML as a native data type. It has pureXML storage, meaning that XML data is stored in XML form, which is a hierarchical structure.

I have some data that I want to store in the DB2 9. Should I store this data as XML schema or relational schema?

This depends on the data you want to store in the database. The main difference between relational schema and XML schema is that relational schema describes data as strongly structured and typed. XML schema describes data as loosely structured and typed. XML schema describes data order, but relational schema does not.
In general, data that has the following properties should be stored in XML:
The data is better described in hierarchal format.
The schema is constantly changing and evolving.
Many attributes of the data are empty or unknown.
You can check http://www.ibm.com/developerworks/xml/library/x-think35.html?S_TACT=105AGX06&S_CMP=EDU for advice on creating XML.

The data which I want to store in the database contains small amount of data, but have a highly complex structure. Should I store this in XML data type?

You should store such data in XML. If you store such data in relational tables, you will have complicated relational schemas, which means you require many tables. Managing these tables can have overhead. The SQL query to access such data requires joining many tables. If you have to process this data together with other data, the SQL query will be even more complicated.

Is there any way to validate the XML data against a XML schema before inserting into the database?

DB2 9 supports validating XML documents with XML schema at insert or import time. The XML schemas have to be registered in XML Schema Repository (XSR) before it can be used for validation. The XML document in the same XML type column can be validated by a different XML schema of your choice in the insert/import time. You can also choose not to validate the XML. In general, there are three choices:
Validate on the server.
Validate within an application.
Do not validate.
XML validation enables you to create complex rules that define acceptable data. You can check http://www.ibm.com/developerworks/xml/library/x-tipxsslt to use the XML Schema Standard Type Library to simplify the process of requiring formatted data such as e-mail addresses and telephone numbers.

When should I choose to validate on Server?

DB2 9 supports validation on the server. You should validate incoming XML documents on the server if the incoming XML documents must be valid, but the XML documents are from an un-trusted source. For instance, suppose that a mortgage company has received an XML document of an application from a mortgage broker. All brokers develop their own applications to fill out the application forms, and to generate XML documents. Because the mortgage company has no control over these applications, and the applications might or might not follow the industrial standard XML schemas, the incoming XML documents are considered as being from an un-trusted source. The mortgage company must ensure that all XML documents are valid, and can validate all XML documents on the server side at insert and import time. The XMLVALIDATE function is used for validating XML documents with an insert statement in an application. The XMLVALIDATE function checks XML documents against the specified XML schema and makes sure that the XML document satisfies the constraints in the XML schema. When validating XML documents, the schema information passed to the XML validate function can be either explicitly passed (explicit validation) or implicitly inferred from the XML document (implicit validation). You can check http://www.ibm.com/developerworks/xml/library/x-style.html for defining data validation rules in XML schema.

What is difference between explicit validation and implicit validation?

The main difference between explicit validation and implicit validation is where the schema information (hints) are provided. For explicit validation, schema information is provided by the ACCORDING TO XMLSCHEMA clause embedded in the insert statement. For implicit validation, schema hints are provided by the inserting XML document.

I want to add XML documents to the DB 2 database. How can I add it?

You can use the insert or import to insert XML data files to DB2 relational tables with XML data type columns. First you have to make sure that you have already created a table with XML column. Then you can use import or insert command to put the data into the database.

I tried to import an XML document to the database, but I am not able to do so. What can be the issue?

You have to make sure that the XML document you are adding is a well formed XML document. You can only import well-formed XML documents because the columns defined as XML data types can only contain complete XML documents. If you are importing a data file with a row containing a document that is not well-formed, it will be rejected by DB2.

How does pureXML parse XML documents?

pureXML performs parsing in 2 ways, implicit parsing and explicit parsing.

Implicit XML parsing occurs when
a. you pass data to the database server using a host variable of type XML, or use a parameter marker of type XML
b. you assign a host variable, parameter marker, or SQL expression with a string data type (character, graphic or binary) to an XML column in an INSERT, UPDATE, DELETE, or MERGE statement.
Explicit parsing occurs when the XMLPARSE function is invoked when inputting XML data. The XMLPARSE function takes a non-XML, character or binary data type as input.

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.