Welcome!

Industrial IoT Authors: Liz McMillan, Kevin Benedict, Elizabeth White, Stackify Blog, Yeshim Deniz

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.

SQL/XML
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.

XMLElement
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.

in ORACLE:
SQL> SELECT
XMLELEMENT("name", expert)
FROM experts
WHERE expert_id BETWEEN 4 and 10;

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

XMLELEMENT("NAME",EXPERT)
--------------------------------------
<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.

XMLAttributes
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.

XMLForest
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
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 www.syscon.com/xmlj/sourcec.cfm. Below is a simpler example, for better understanding.


SQL>  CREATE OR REPLACE VIEW expert_view of XMLTYPE WITH OBJECT ID
(EXTRACT(sys_nc_rowinfo$,'/experts/expert/@id').getnumberval()) AS 
SELECT XMLELEMENT("experts",
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;

SYS_NC_ROWINFO$

<experts>
  <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>
</experts>

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;
EXTRACT(VALUE(X),'/EXPERTS/EXPERT[@ID=4]')

<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;

X.ISSCHEMAVALID('EXPERT_VIEW.XSD','EXPERTS')

1

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 www.sys-con.com/xmlj/sourcec.cfm). 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.
Conclusion
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.

References

  • 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" www.cs.umb.edu/efg
  • Oracle9i Database Online Documentation http://otn.oracle.com/pls/db92/db92.homepage
  • 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
    As data explodes in quantity, importance and from new sources, the need for managing and protecting data residing across physical, virtual, and cloud environments grow with it. Managing data includes protecting it, indexing and classifying it for true, long-term management, compliance and E-Discovery. Commvault can ensure this with a single pane of glass solution – whether in a private cloud, a Service Provider delivered public cloud or a hybrid cloud environment – across the heterogeneous enter...
    Cloud-enabled transformation has evolved from cost saving measure to business innovation strategy -- one that combines the cloud with cognitive capabilities to drive market disruption. Learn how you can achieve the insight and agility you need to gain a competitive advantage. Industry-acclaimed CTO and cloud expert, Shankar Kalyana presents. Only the most exceptional IBMers are appointed with the rare distinction of IBM Fellow, the highest technical honor in the company. Shankar has also receive...
    "We view the cloud not as a specific technology but as a way of doing business and that way of doing business is transforming the way software, infrastructure and services are being delivered to business," explained Matthew Rosen, CEO and Director at Fusion, in this SYS-CON.tv interview at 18th Cloud Expo (http://www.CloudComputingExpo.com), held June 7-9 at the Javits Center in New York City, NY.
    The Founder of NostaLab and a member of the Google Health Advisory Board, John is a unique combination of strategic thinker, marketer and entrepreneur. His career was built on the "science of advertising" combining strategy, creativity and marketing for industry-leading results. Combined with his ability to communicate complicated scientific concepts in a way that consumers and scientists alike can appreciate, John is a sought-after speaker for conferences on the forefront of healthcare science,...
    WebRTC is great technology to build your own communication tools. It will be even more exciting experience it with advanced devices, such as a 360 Camera, 360 microphone, and a depth sensor camera. In his session at @ThingsExpo, Masashi Ganeko, a manager at INFOCOM Corporation, introduced two experimental projects from his team and what they learned from them. "Shotoku Tamago" uses the robot audition software HARK to track speakers in 360 video of a remote party. "Virtual Teleport" uses a multip...
    Data is the fuel that drives the machine learning algorithmic engines and ultimately provides the business value. In his session at Cloud Expo, Ed Featherston, a director and senior enterprise architect at Collaborative Consulting, discussed the key considerations around quality, volume, timeliness, and pedigree that must be dealt with in order to properly fuel that engine.
    In his session at Cloud Expo, Alan Winters, U.S. Head of Business Development at MobiDev, presented a success story of an entrepreneur who has both suffered through and benefited from offshore development across multiple businesses: The smart choice, or how to select the right offshore development partner Warning signs, or how to minimize chances of making the wrong choice Collaboration, or how to establish the most effective work processes Budget control, or how to maximize project result...
    "Akvelon is a software development company and we also provide consultancy services to folks who are looking to scale or accelerate their engineering roadmaps," explained Jeremiah Mothersell, Marketing Manager at Akvelon, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    IoT is rapidly becoming mainstream as more and more investments are made into the platforms and technology. As this movement continues to expand and gain momentum it creates a massive wall of noise that can be difficult to sift through. Unfortunately, this inevitably makes IoT less approachable for people to get started with and can hamper efforts to integrate this key technology into your own portfolio. There are so many connected products already in place today with many hundreds more on the h...
    DXWorldEXPO LLC announced today that ICC-USA, a computer systems integrator and server manufacturing company focused on developing products and product appliances, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City. ICC is a computer systems integrator and server manufacturing company focused on developing products and product appliances to meet a wide range of ...
    JETRO showcased Japan Digital Transformation Pavilion at SYS-CON's 21st International Cloud Expo® at the Santa Clara Convention Center in Santa Clara, CA. The Japan External Trade Organization (JETRO) is a non-profit organization that provides business support services to companies expanding to Japan. With the support of JETRO's dedicated staff, clients can incorporate their business; receive visa, immigration, and HR support; find dedicated office space; identify local government subsidies; get...
    René Bostic is the Technical VP of the IBM Cloud Unit in North America. Enjoying her career with IBM during the modern millennial technological era, she is an expert in cloud computing, DevOps and emerging cloud technologies such as Blockchain. Her strengths and core competencies include a proven record of accomplishments in consensus building at all levels to assess, plan, and implement enterprise and cloud computing solutions. René is a member of the Society of Women Engineers (SWE) and a m...
    Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at @ThingsExpo, James Kirkland, Red Hat's Chief Archi...
    In his general session at 19th Cloud Expo, Manish Dixit, VP of Product and Engineering at Dice, discussed how Dice leverages data insights and tools to help both tech professionals and recruiters better understand how skills relate to each other and which skills are in high demand using interactive visualizations and salary indicator tools to maximize earning potential. Manish Dixit is VP of Product and Engineering at Dice. As the leader of the Product, Engineering and Data Sciences team at D...
    Personalization has long been the holy grail of marketing. Simply stated, communicate the most relevant offer to the right person and you will increase sales. To achieve this, you must understand the individual. Consequently, digital marketers developed many ways to gather and leverage customer information to deliver targeted experiences. In his session at @ThingsExpo, Lou Casal, Founder and Principal Consultant at Practicala, discussed how the Internet of Things (IoT) has accelerated our abilit...
    Organizations planning enterprise data center consolidation and modernization projects are faced with a challenging, costly reality. Requirements to deploy modern, cloud-native applications simultaneously with traditional client/server applications are almost impossible to achieve with hardware-centric enterprise infrastructure. Compute and network infrastructure are fast moving down a software-defined path, but storage has been a laggard. Until now.
    Digital Transformation is much more than a buzzword. The radical shift to digital mechanisms for almost every process is evident across all industries and verticals. This is often especially true in financial services, where the legacy environment is many times unable to keep up with the rapidly shifting demands of the consumer. The constant pressure to provide complete, omnichannel delivery of customer-facing solutions to meet both regulatory and customer demands is putting enormous pressure on...
    The best way to leverage your CloudEXPO | DXWorldEXPO presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering CloudEXPO | DXWorldEXPO will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at CloudEXPO. Product announcements during our show provide your company with the most reach through our targeted audienc...
    @DevOpsSummit at Cloud Expo, taking place November 12-13 in New York City, NY, is co-located with 22nd international CloudEXPO | first international DXWorldEXPO and will feature technical sessions from a rock star conference faculty and the leading industry players in the world.
    DXWorldEXPO LLC announced today that the upcoming DXWorldEXPO | CloudEXPO New York event will feature 10 companies from Poland to participate at the "Poland Digital Transformation Pavilion" on November 12-13, 2018.