Click here to close now.


Industrial IoT Authors: Peter Silva, Ian Khan, Pat Romanski, Liz McMillan, Elizabeth White

Related Topics: Industrial IoT

Industrial IoT: Article

Generating XML from Relational Database Tables

This Emerging Standard Enables to Generate XML Fragments from Relational Data

This article looks in detail at how to generate XML data from your relational database. Although the examples were run on Oracle, very little of the code is Oracle specific. You can easily use all the ideas and examples presented here in other relational databases. We did this project at University of Massachusetts Boston as part of the Electronic Field Guide (EFG) project.

XML is the de facto standard for data exchange. It's simple, Unicode based, and platform independent. XML is a metadata language; it contains information about the data. All these features make it an attractive standard for exchanging data.

Why Generate XML from Relational Data?
Today most data (80% or more), is stored in relational databases such as Oracle, DB2, SQL Server 2000, and others. The Internet and Web services are present in our daily lives. A tremendous amount of data is transferred over the Internet between businesses. Data transfers may happen within a company, between different branches, or between different companies and individuals. No matter which of these situations applies to you, it's very likely that you will be asked to ship your data in XML, or that you are already doing it. Relational Database Management Systems (RDBMS) are still the best way to hold data in bulk. In the following paragraph we will give some information about our project and related resources that we used to provide relational data in XML.

Our Project
Our XML project is part of the Electronic Field Guild (EFG). The EFG project is an object-oriented, Web-based database for the identification of species and recording of ecological observations. With funding from the National Science Foundation, this project is the result of the collaborative efforts between the Departments of Computer Science and Biology at the University of Massachusetts Boston. EFG queries the Integrated Taxonomic Information System (ITIS). On each query we only get a very small part of the ITIS database. XML can describe exactly what data it is delivering, and thus is the preferred format for the query results.

There are three ITIS branches: Canadian, Mexican, and U.S. U.S. ITIS provides the whole data in bulk (about 85MB), but, like many other organizations, it has been slow to join the modern trend to answer queries on it in XML format. Canadian ITIS provides query results in XML, but due to networking delays and some other problems, getting the result takes a long time. We decided to bring the whole database home and return the results of queries in XML format. We are using an RDBMS to store the bulk data. In the following paragraphs we will explain how to generate XML from this relational data.

This emerging standard enables us to generate XML fragments from relational data. Oracle and many other relational databases support the following standard SQL/XML functions. We will explain most of the SQL/XML functions, XMLElement, XMLAttributes, XMLForest, XMLAgg, and give an example for each.

As its name implies, this function generates an XML element. It takes an element name, an optional collection of attributes for the element, and zero or more arguments that make up the element content and returns an instance of type XMLType.

Table 1 is the schema of the "experts" table from our database. All the simple examples are related to this table, and Oracle was used for most cases.

Table 2 shows some of the data from the experts table.

XMLELEMENT("name", expert)
FROM experts
WHERE expert_id BETWEEN 4 and 10;

in DB2:
XML2CLOB( XMLELEMENT(NAME "name", expert) )
FROM experts
WHERE expert_id BETWEEN 4 and 10

<name>Stotler, Raymond E.</name>
<name>Alfred L. Gardner</name>
<name>Steve J. Upton</name>
<name>Wayne Starnes</name>
<name>Lynne Parenti</name>

Here, name is the tag name; expert is the corresponding column name in the experts table. This query obtains the expert column value from the experts table and puts <name> and </name> tags around it.

This function simply creates attributes for an element. Listing 1 shows how it works.

Each name element has an id attribute. Each id attribute is obtained from the expert_id column value of the experts table. The XMLAttributes clause isn't used alone; it's used inside an XMLElement function. This makes sense since, in XML, an attribute is a name-value pair attached to the associated element's start tag.

The XMLForest() function produces a forest of XML elements from the given list of arguments. In other words, it produces many XML elements at a time.

In Listing 2, expert is the root element. The id, name, and info elements are children of expert element. The XMLForest function created three elements: the id element corresponds to expert_id column value; the name element corresponds to expert column value; and the info element corresponds to the exp_comment column value in experts table. Naming the elements in this function is accomplished by using AS keyword. For the first element it is expert_id as "id".

What if you don't specify an id (id = 6 in this example)? In this case you will have the appropriate XML fragment for each row.

XMLAgg() is an aggregate function that produces a forest of XML elements derived from a set of rows.

In Listing 3, there are five name elements as children of the experts root element. Without using XMLAgg() function it's impossible to have many name elements in a single XML document. If you don't use the XMLAgg() function and instead submit the query, you will get Listing 4.

That's not what we wanted. For this output, each experts element has only one name child element, and there are many experts elements. To combine information from multiple rows of the table we need to use the XMLAgg() function.

Creating hierarchical data is easy. Listing 5 shows you how to do this. You can use this idea in similar queries. This example involves the taxonomic_units table, which stores data about species, phyla, etc., i.e. nodes in the tree of life. Note the select within the select, to run through the inner loop.

I hope these examples have convinced you that it's possible to generate XML that obeys any XML Schema or DTD. You can use these functions, nested in each other, to display any kind of parent-child relation. Another such query could display all the experts for each taxon.

This is easy! You can start using these functions as soon as you finish reading this article. Being flexible adds value to this approach. An important point that's worth mentioning is that all the work is done inside the database by using the SQL/XML functions. In the Oracle case, all the work is done in the XML DB Engine. This is much faster and more efficient than doing the work outside the database. Another approach would be to get the data from the database and tag it outside the database for creating XML. This is possible but less efficient, more time consuming. The following are more advanced topics, such as XMLType View and XML Schema validation.

XMLType View
A view is a table that results from a subquery, but which has its own name and can be treated in most ways as if it were an ordinary table. Thus a view table is a logical window on selected data from the base tables and other views. XMLType views wrap the relational data in XML formats. In other words you can have a virtual XML file over your relational data inside the database. We can treat this new view as XML, and use XML specific operations on it, such as XPath and XQuery. These types of operations will be converted to corresponding SQL; this is known as query rewrite. You can create this view by using SQL/XML functions. Our SQL, which generates an XMLType view is shown in the source code, create.sql, online at Below is a simpler example, for better understanding.

(EXTRACT(sys_nc_rowinfo$,'/experts/expert/@id').getnumberval()) AS 
XMLAGG( XMLELEMENT("expert", XMLATTRIBUTES(expert_id as "id"), expert)))
  	 FROM experts
WHERE expert_id BETWEEN 4 and 10;

View created.

This is how the view looks when you query it. This XMLType view is created over the experts table. The data displayed comes from the underlying relational table.

SQL> SELECT * FROM expert_view;


  <expert id="4">Stotler, Raymond E.</expert>
  <expert id="5">Alfred L. Gardner</expert>
  <expert id="6">Steve J. Upton</expert>
  <expert id="8">Wayne Starnes</expert>
  <expert id="10">Lynne Parenti</expert>

It's possible to use XPath expressions on this view. Following is a query that has an XPath expression. This query extracts the value of an expert element that has an id attribute equal to 4.

SQL> SELECT extract(value(x), '/experts/expert[@id=4]') FROM expert_view x;

<expert id="4">Stotler, Raymond E.</expert>

First Step: Register an XSD
You can validate an XMLType instance against XML Schema Documentation (XSD) in Oracle. First, register the XSD in Oracle. Next, call a function that does the validation. There are several functions in Oracle for accomplishing this. Listing 6 is the only one shown, for simplicity.

This will register the XSD and name it as expert_view.xsd. You can name it anything you want of course.

Second Step: Validation
This section introduces isSchemaValid (argumet1, argument2). The first argument is the name of the registered XSD. The second argument is the name of the root element in the specified XSD. XSD can have more than one root. If the XML document (x in this case) is valid, this isSchemaValid returns 1.

SQL> select x.isSchemaValid('expert_view.xsd', 'experts') from expert_view x;



Putting It Together
For our project we generated XML that obeys our XML Schema Documentation (XSD)[itis.xsd]. Our SQL is shown in the source code for this article (available online at It will output all the data in XML. As you can see, this query is significantly nested and long, but it's easy to understand (I hope). There are a few more functions in this query that we would like to mention: trim() simply gets rid of the white space in an entry and the to_char() function is used to change the date format. For example, to_char(v.up date_date, 'YYYY-MM-DD') will display the date as something like 2004-04-23. This is somewhat important because the date format in the database doesn't match the date format of the XML Schema.

XML Delivery
In this project we deliver XML through a search server. Using this server, you can query the database by tsn, which is identical to the identification number of a taxonomic unit. e.g. a particular animal or plant. For this part of the project we used:

  1. Sun Enterprise 250 Server: 2 X UltraSPARC-II 400MHz, 2GB memory, SCSI disks
  2. Sun Solaris Operating System 5.8
  3. Oracle 9.2i
  4. JAVA 1.4.2
  5. Java Database Connectivity (JDBC) inside JavaBeans for connecting to and querying the database. We preferred the Oracle thin driver to the oci driver.
  6. JavaServer Pages (JSP) at the user interface level
  7. Tomcat 5.0.12
JSP gets a request from the user, and passes it to a JavaBean. The JavaBean connects to Oracle using JDBC, gets information from the database, and passes it back to the JSP. For better performance:
  • Use Oracle Connection Pool
  • Turn off the auto commit feature
  • Use predefined column types in the select statement.
If you are not already doing so, you will probably be delivering your data in XML format soon. Keep in mind that most data resides in relational databases so generating XML from relational databases will be a very common task. Many of the database vendors, such as Oracle and DB2, provide the SQL/XML function support to make this task easier. SQL Server 2000 adds a new clause, the FOR XML clause, to the SELECT statement, which instructs SQL Server to return the result of a query in XML format.

Based on our experiments, generating XML data inside the database is fast; it took an average of 0.032 seconds per taxon query. Storing your data without the start and end tags is space efficient. Relational databases are very mature about storing, querying, and retrieving data quickly and efficiently. There has been over 20 years of work done in these technologies.

For larger projects, having XMLType views over the relational tables is advantageous. It gives you the chance to query the data using XPath and XQuery besides SQL.

In this article we provided some insight about SQL/XML functions, mentioned the benefits of storing data in relational databases, and explored the benefits of using XMLType views over relational data. For more detailed information about SQL/XML functions, check your database documentation.


  • O'Neil, Patrick and Elizabeth. (2000) Database:Principles, Programming, Performance. Morgan Kauffman.
  • and "Electronic Field Guide: An Object-Oriented WWW Database to Identify Species and Record Ecological Observations"
  • Oracle9i Database Online Documentation
  • Appelquist, Daniel K. (2001). XML and SQL: Developing Web Applications. Addison-Wesley.
  • Katz, Howard; and Chamberlin, D.D. (2003) XQuery from the Experts: A Guide to the W3C XML Query Language. Addison-Wesley.
  • More Stories By Selim Mimaroglu

    Selim Mimaroglu is a PhD candidate in computer science at the University of Massachusetts in Boston. He holds an MS in computer science from that school and has a BS in electrical engineering.

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

    @ThingsExpo Stories
    “In the past year we've seen a lot of stabilization of WebRTC. You can now use it in production with a far greater degree of certainty. A lot of the real developments in the past year have been in things like the data channel, which will enable a whole new type of application," explained Peter Dunkley, Technical Director at Acision, in this interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
    NHK, Japan Broadcasting, will feature the upcoming @ThingsExpo Silicon Valley in a special 'Internet of Things' and smart technology documentary that will be filmed on the expo floor between November 3 to 5, 2015, in Santa Clara. NHK is the sole public TV network in Japan equivalent to the BBC in the UK and the largest in Asia with many award-winning science and technology programs. Japanese TV is producing a documentary about IoT and Smart technology and will be covering @ThingsExpo Silicon Valley. The program, to be aired during the peak viewership season of the year, will have a major impac...
    "Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
    Developing software for the Internet of Things (IoT) comes with its own set of challenges. Security, privacy, and unified standards are a few key issues. In addition, each IoT product is comprised of at least three separate application components: the software embedded in the device, the backend big-data service, and the mobile application for the end user's controls. Each component is developed by a different team, using different technologies and practices, and deployed to a different stack/target - this makes the integration of these separate pipelines and the coordination of software upd...
    You have your devices and your data, but what about the rest of your Internet of Things story? Two popular classes of technologies that nicely handle the Big Data analytics for Internet of Things are Apache Hadoop and NoSQL. Hadoop is designed for parallelizing analytical work across many servers and is ideal for the massive data volumes you create with IoT devices. NoSQL databases such as Apache HBase are ideal for storing and retrieving IoT data as “time series data.”
    There are so many tools and techniques for data analytics that even for a data scientist the choices, possible systems, and even the types of data can be daunting. In his session at @ThingsExpo, Chris Harrold, Global CTO for Big Data Solutions for EMC Corporation, will show how to perform a simple, but meaningful analysis of social sentiment data using freely available tools that take only minutes to download and install. Participants will get the download information, scripts, and complete end-to-end walkthrough of the analysis from start to finish. Participants will also be given the pract...
    Clearly the way forward is to move to cloud be it bare metal, VMs or containers. One aspect of the current public clouds that is slowing this cloud migration is cloud lock-in. Every cloud vendor is trying to make it very difficult to move out once a customer has chosen their cloud. In his session at 17th Cloud Expo, Naveen Nimmu, CEO of Clouber, Inc., will advocate that making the inter-cloud migration as simple as changing airlines would help the entire industry to quickly adopt the cloud without worrying about any lock-in fears. In fact by having standard APIs for IaaS would help PaaS expl...
    SYS-CON Events announced today that Luxoft Holding, Inc., a leading provider of software development services and innovative IT solutions, has been named “Bronze Sponsor” of SYS-CON's @ThingsExpo, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Luxoft’s software development services consist of core and mission-critical custom software development and support, product engineering and testing, and technology consulting.
    SYS-CON Events announced today that ProfitBricks, the provider of painless cloud infrastructure, will exhibit at SYS-CON's 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. ProfitBricks is the IaaS provider that offers a painless cloud experience for all IT users, with no learning curve. ProfitBricks boasts flexible cloud servers and networking, an integrated Data Center Designer tool for visual control over the cloud and the best price/performance value available. ProfitBricks was named one of the coolest Clo...
    Organizations already struggle with the simple collection of data resulting from the proliferation of IoT, lacking the right infrastructure to manage it. They can't only rely on the cloud to collect and utilize this data because many applications still require dedicated infrastructure for security, redundancy, performance, etc. In his session at 17th Cloud Expo, Emil Sayegh, CEO of Codero Hosting, will discuss how in order to resolve the inherent issues, companies need to combine dedicated and cloud solutions through hybrid hosting – a sustainable solution for the data required to manage I...
    WebRTC is about the data channel as much as about video and audio conferencing. However, basically all commercial WebRTC applications have been built with a focus on audio and video. The handling of “data” has been limited to text chat and file download – all other data sharing seems to end with screensharing. What is holding back a more intensive use of peer-to-peer data? In her session at @ThingsExpo, Dr Silvia Pfeiffer, WebRTC Applications Team Lead at National ICT Australia, will look at different existing uses of peer-to-peer data sharing and how it can become useful in a live session to...
    Mobile messaging has been a popular communication channel for more than 20 years. Finnish engineer Matti Makkonen invented the idea for SMS (Short Message Service) in 1984, making his vision a reality on December 3, 1992 by sending the first message ("Happy Christmas") from a PC to a cell phone. Since then, the technology has evolved immensely, from both a technology standpoint, and in our everyday uses for it. Originally used for person-to-person (P2P) communication, i.e., Sally sends a text message to Betty – mobile messaging now offers tremendous value to businesses for customer and empl...
    SYS-CON Events announced today that IBM Cloud Data Services has been named “Bronze Sponsor” of SYS-CON's 17th Cloud Expo, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. IBM Cloud Data Services offers a portfolio of integrated, best-of-breed cloud data services for developers focused on mobile computing and analytics use cases.
    Scott Guthrie's keynote presentation "Journey to the intelligent cloud" is a must view video. This is from AzureCon 2015, September 29, 2015 I have reproduced some screen shots in case you are unable to view this long video for one reason or another. One of the highlights is 3 datacenters coming on line in India.
    Nowadays, a large number of sensors and devices are connected to the network. Leading-edge IoT technologies integrate various types of sensor data to create a new value for several business decision scenarios. The transparent cloud is a model of a new IoT emergence service platform. Many service providers store and access various types of sensor data in order to create and find out new business values by integrating such data.
    Apps and devices shouldn't stop working when there's limited or no network connectivity. Learn how to bring data stored in a cloud database to the edge of the network (and back again) whenever an Internet connection is available. In his session at 17th Cloud Expo, Bradley Holt, Developer Advocate at IBM Cloud Data Services, will demonstrate techniques for replicating cloud databases with devices in order to build offline-first mobile or Internet of Things (IoT) apps that can provide a better, faster user experience, both offline and online. The focus of this talk will be on IBM Cloudant, Apa...
    The enterprise is being consumerized, and the consumer is being enterprised. Moore's Law does not matter anymore, the future belongs to business virtualization powered by invisible service architecture, powered by hyperscale and hyperconvergence, and facilitated by vertical streaming and horizontal scaling and consolidation. Both buyers and sellers want instant results, and from paperwork to paperless to mindless is the ultimate goal for any seamless transaction. The sweetest sweet spot in innovation is automation. The most painful pain point for any business is the mismatch between supplies a...
    As a company adopts a DevOps approach to software development, what are key things that both the Dev and Ops side of the business must keep in mind to ensure effective continuous delivery? In his session at DevOps Summit, Mark Hydar, Head of DevOps, Ericsson TV Platforms, will share best practices and provide helpful tips for Ops teams to adopt an open line of communication with the development side of the house to ensure success between the two sides.
    As more and more data is generated from a variety of connected devices, the need to get insights from this data and predict future behavior and trends is increasingly essential for businesses. Real-time stream processing is needed in a variety of different industries such as Manufacturing, Oil and Gas, Automobile, Finance, Online Retail, Smart Grids, and Healthcare. Azure Stream Analytics is a fully managed distributed stream computation service that provides low latency, scalable processing of streaming data in the cloud with an enterprise grade SLA. It features built-in integration with Azur...
    WebRTC: together these advances have created a perfect storm of technologies that are disrupting and transforming classic communications models and ecosystems. In his session at WebRTC Summit, Cary Bran, VP of Innovation and New Ventures at Plantronics and PLT Labs, will provide an overview of this technological shift, including associated business and consumer communications impacts, and opportunities it may enable, complement or entirely transform.