I have XML with n- tier structure (there are n level sub nodes in each node). I have to design a DB schema in any relational DBMS so all the data will be stored in the database. I want to know how I can store this n-tier structure in some 3 table schema. Is it possible? If so, how? If not, is there another method that would accomplish the same thing?


Scott Howard’s Answer: Most RDBMS vendors have some capability to shred XML into single tables; what you ask for is unique and very desirable. The hierarchical relationships imbedded in most XML documents should be mapped to multiple relational tables in order to preserve the relationships represent by that hierarchy. Attempting to simply shred them into a single table destroys those relationships. It would be like trying to represent complex relationships in first normal form. You also can’t limit yourself to only a three-table schema as you need to represent your hierarchical relationships in as many table as it takes.

The method for creating these schemas is straightforward and requires detailed knowledge of both XML and the relational model. However correctly modeling these relationships is both error prone and time-consuming. As the volume of XML content continue to grow at exponential rates, those practicing these manual processes will quickly become overwhelmed. Consider tools. That is a tool that will support any environment with which you may find yourself.

I recommend that you look at DB2 Information Integrator regardless of your current RDBMS of choice. DB2 II has the ability to efficiently work with most major RDBMSs and can read and transform XML to the relational model regardless of the number of levels actually representing in the hierarchy. They call the feature "discovery." These mappings can then be applied to any major RDBMS through a feature they refer to as DDL Transparency and populated by a simple INSERT based on SELECT. The beauty of this Information Integration technology is that though it is DB2 based it allows you to keep the data where it makes the most sense, managed by its original applications – it does not require or even recommend moving the data from its original source. There is really no need to move data as Information Integrator’s Global Optimizer does a great job choosing the most efficient access path and strategy.

Have you also considered the need to generate or publish XML documents from your database? If you haven’t the need will soon arise and again you should seek a tool that works for any environment. Not only can Information Integrator generate and publish XML documents via built- in function from its own local DB2 data source – this would not be too special as most RDBMSs can also brag about this – but can dynamically assemble XML content from a variety of relational and non-relational data sources simultaneously in real time. It can actually join data natively from DB2, Oracle, Informix, Microsoft SQL Server, Sybase, Teradata, flat files, XML documents spreadsheets and more using the results to publish XML. Granted that you should not need to access all of those at once, but they are your options.

IBM touts this technology as the direct result of a closer relationship between IBM research which invented the technology and development which quickly implemented it. I have not been this impressed with a technology’s potential impact on BI and data warehousing for some time. If fact it even recently drew some misguided ire from Bill Inmon. Try the technology for yourself and you will quickly refute Inmon’s conclusion.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access