XML Management

[s.piyush58]

Hi, I am new to this...so hope I can get some help from you all...

I get XML feeds as input and have to store this data on our server. I have worked with databases but new to XML. Can someone tell me how I can store and manage this data?

[apuri976]

You can store the XML document in the file system (i.e. as a file on the server) and read the data from it by using standard file system functions. Most of the technologies (J2EE, .NET, PHP, CGI/Perl, etc) have in-built libraries to work with XML files.

[s.piyush58]

I have data in database and a lot of data resides as XML files as well. I do not want to maintain different data stores having the data in database and XML data in files. Is there any way to manage both XML and relational data together in a single data store?

[apuri976]

If you do not want to store the XML data as files and want to maintain the complete data in a database, you can store the xml document into the database as BLOB or CLOB type. This would store the XML file in the binary format. This approach is recommended only if you want to store or retrieve the XML file in its entirety. But retrieving a specific element/attribute from XML document would have performance constraints as the complete XML document has to be loaded to fetch even a single record.

[karsoods53]

I am working on an application for data storage which deals mostly with XML data. I am storing the XML data as CLOB data type. Is there any other way to work with XML data?

[Ppatil6574]

Storing the XML as CLOB data type has its drawbacks. Searching and retrieving a subset of the document would be slow. The XML document needs to be parsed for each query to determine which portions of the document satisfy the specified search criteria. Furthermore, updating large objects would also take time. Your applications must provide the entire XML document to the DBMS for update, even if it only changed a small portion of it. This can result in unacceptably high processing costs, particularly if the XML document is very large

[karsoods53]

Are there any other ways to store XML data in database?

[Ppatil6574]

Alternatively, you can split the elements and attributes of the XML document to relational columns in one or more tables. Storing the data like this would have several advantages. You can directly work with selected portions of the “converted” XML data. You can add index to columns to make data retrieval more efficient and fast. You can make use of SQL queries to retrieve the required piece of data.

[apuri976]

But decomposing the XML would be difficult (some time impossible) if XML documents contain heavily nested parent/child relationships and irregular structures. Shredding such documents may also require a large number of tables. Querying a “shredded” document would require complex SQL statements and it may include many joins. It would take time to write these queries. It will give runtime productivity and performance problems. Moreover, it is very difficult , if not impossible to recover (compose ) the XML document from these relational tables

[mallar6]

You can use pureXML (DB2 from IBM) to store the XML data in the database. It stores the XML document natively in the database which means XML is not stored as CLOB or shredded into tables. XML remains XML even inside the database. The data can be queried using SQL or XQuery. Searching and retrieving subsets of XML would be very fast as XML is not parsed during querying.

[karsoods53]

I do not want to store only XML documents. I also have other data types in my application

[mallar6]

DB2 is not a XML-only database. DB2 provides integration of relational data and XML data within a single database. Tables created with XML data types may also contain columns with “traditional” SQL data types, including numeric data, character strings, date/time data, and others.

[karsoods53]

I have not worked with pureXML. How difficult it is to query the XML data from database?

[mallar6]

It is very easy to work with pureXML. If you're already familiar with SQL, you can immediately apply your skills to working with XML data stored natively in DB2. SQL/XML or Xquery statements are used to query the data. Both relational and XML data can be requested within a single request. Please check http://www.ibm.com/developerworks/db2/library/techarticle/dm-0603saracco2/ for more details.

[karsoods53]

Is it possible to add indexes to XML data type?

[mallar6]

pureXML has new indexing technology; Indexes can be created for specific elements or attributes of the XML documents to speedup the data retrieval. It helps those queries which require specific portions of XML documents. Full text search over XML documents is also supported, and specialized text indexes can be defined to improve performance of such searches

[s.piyush58]

pureXML stores XML data in database retaining the XML format. Is it possible to index the columns? This could give us better query performance. :-/

[mallar6]

Yes. pureXML has an indexing feature that can index over XML columns. With this indexing feature, retrieving XML node sets is much faster compared to retrieving nodes from XML documents stored in CLOB.

[karsoods53]

Are the XML documents stored in the same area as the relational data?

[mallar6]

If you create a table which has an integer column and an XML column, the integer column (relational data) is stored in same table , but DB2 does not store the XML document in same table. Instead, the XML column contains an XML data descriptor that has the document ID and version ID for the XML document. The document is stored in the XML Data Area (XDA), which is separate from the base table.